AVERAGEIFS gives you the average size of a transaction in a category, not the average you spend in that category each month. For the transaction average, use =AVERAGEIFS($C$2:$C$500,$B$2:$B$500,"Dining out"). For the monthly average, divide a SUMIFS total by the number of months, such as =SUMIFS($C$2:$C$500,$B$2:$B$500,"Dining out")/3.
What does AVERAGEIFS actually average?
Say your sheet has dates in column A, categories in B and amounts in C. AVERAGEIFS takes the amounts that meet your condition and divides their total by how many there are. Every matching row is one data point, so a month with twelve small payments counts twelve times and a month with one big payment counts once. That is the average ticket size, and it answers the question of how much a typical payment is.
Watch the argument order. In AVERAGEIFS the range to average comes first: AVERAGEIFS(average_range, criteria_range1, criteria1, ...). In the older AVERAGEIF the range to average comes last. Mixing them up gives wrong numbers rather than an error when the ranges happen to be compatible.
Why is the average spend per month different?
Invented figures for three months of dining out: January has 5 payments totalling ₹7,500, February has 4 payments totalling ₹4,800 and March has 3 payments totalling ₹5,700. Together that is 12 payments and ₹18,000.
| Measure | Working | Result |
|---|---|---|
| Average per transaction | ₹18,000 / 12 payments | ₹1,500 |
| Average per month | ₹18,000 / 3 months | ₹6,000 |
| Busiest month | January | ₹7,500 |
If you used the transaction average of ₹1,500 as a monthly budget, you would plan to spend a quarter of what you actually do.
How do I calculate the monthly average properly?
- Put the first and last month you are measuring in two cells, say G1 and G2, as the first day of each month.
- Count the months in H1: =DATEDIF(G1,G2,"m")+1.
- Total the category: =SUMIFS($C$2:$C$500,$B$2:$B$500,"Dining out",$A$2:$A$500,">="&G1,$A$2:$A$500,"<"&EDATE(G2,1)).
- Divide the total by H1.
Dividing by the number of months, not the number of months that had a transaction, is deliberate. A category you skipped for a month really did cost you zero that month.
Say you want the average for a financial year that runs April to March. Put 1-Apr-2025 in G1 and 1-Mar-2026 in G2, and the same steps work. The count in H1 is 12, so the monthly average is the year's total divided by 12.
Can I limit AVERAGEIFS to a date range?
Yes, add pairs just as you would in SUMIFS: =AVERAGEIFS($C$2:$C$500,$B$2:$B$500,"Dining out",$A$2:$A$500,">="&G1,$A$2:$A$500,"<"&EDATE(G2,1)). Wrap it as =IFERROR(that formula,0) if there may be no matching rows, because AVERAGEIFS returns #DIV/0! when nothing matches.
What should I watch out for?
- Refunds entered as negative amounts are averaged in, lowering the typical payment. Decide whether to keep them in a separate column.
- Text amounts are skipped silently, which makes the average look higher than reality. Test with =COUNT($C$2:$C$500) against the number of rows.
- Very large one-off payments distort an average. Compare it with the median using =MEDIAN(IF($B$2:$B$500="Dining out",$C$2:$C$500)), entered with Ctrl+Shift+Enter in older Excel, or as a normal formula in Excel 365.
- Rent, EMIs and school fees are mostly one fixed payment a month, so their transaction average and monthly average are almost the same. The difference matters for flexible categories.
Another way to see the monthly average is a small table with one row per month, each using SUMIFS with the month's date range, and then =AVERAGE over the monthly totals. A month with no spending then shows as 0 and is counted, which is what you want, and the table also shows which months pulled the average up.
When a sheet stops being enough
If building these averages every month feels like a chore, SpendixAI offers budgets on Pro and reports you can filter and group by category. It does not replace thinking about what a reasonable number is, but it removes the formula upkeep.
Next step
Pick one category you suspect is growing, calculate both averages for the last six months, and note which of the two moved.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

