Use COUNTIF when you have one condition, such as a category, and COUNTIFS when you need two or more, such as a category in a particular month. For example =COUNTIFS($B$2:$B$500,"Food",$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&EDATE($F$1,1)) counts Food transactions in the month that begins on the date in F1. Divide the SUMIFS total by that count and you get the average ticket size.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one criterion: =COUNTIF($B$2:$B$500,"Food"). COUNTIFS takes pairs of range and criterion, and a row is counted only when every pair is true. All the ranges in COUNTIFS must be the same size, or Excel returns #VALUE!. If you have only one condition, COUNTIFS gives the same answer as COUNTIF, so some people use COUNTIFS for everything.
Say your sheet has dates in column A, categories in B and amounts in C. The dates must be real dates, not text that merely looks like dates.
How do I count transactions for one category in one month?
- Type the first day of the month in F1, for example 1-Mar-2026, formatted as a date.
- Type the category in G1, for example Food.
- In H1 enter =COUNTIFS($B$2:$B$500,$G$1,$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&EDATE($F$1,1)).
- Change F1 or G1 to see another month or category.
The "<"&EDATE($F$1,1) part means before the first day of the next month, so you never have to work out whether a month ends on the 28th, 30th or 31st.
How do I get the average ticket size?
Average ticket size is just total spent divided by number of transactions. In I1 enter =IFERROR(SUMIFS($C$2:$C$500,$B$2:$B$500,$G$1,$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&EDATE($F$1,1))/H1,0). IFERROR stops a #DIV/0! showing when the category had no transactions that month.
Here is a worked example with invented figures for one month:
| Category | Transactions | Total (₹) | Average ticket (₹) |
|---|---|---|---|
| Food delivery | 18 | 14,400 | 800 |
| Groceries | 9 | 13,500 | 1,500 |
| Fuel | 4 | 5,200 | 1,300 |
| Cab rides | 12 | 3,600 | 300 |
The totals tell you where the money went. The counts tell you how often a habit shows up. Food delivery costs less in total than groceries here but happens twice as often, which is a different kind of problem to fix.
Can I count by category and by month in one grid?
Yes. Put categories down column K and the first day of each month across row 1, starting at L1. In L2 enter =COUNTIFS($B$2:$B$500,$K2,$A$2:$A$500,">="&L$1,$A$2:$A$500,"<"&EDATE(L$1,1)) and fill right and down. The dollar signs lock the category column and the month row, so one formula fills the whole grid.
Why does COUNTIFS return 0 or too few?
- The category has a trailing space, so "Food " does not equal "Food". Clean it with =TRIM(B2) in a helper column.
- Dates are text. Test with =ISNUMBER(A2); if it says FALSE, convert the dates first.
- The criteria were typed as ">=F1" without the & joining, so Excel looked for the literal text F1.
- The ranges are different sizes, for example $A$2:$A$500 against $B$2:$B$400.
When a sheet stops being enough
If you are counting by hand because your statements arrive every month as PDF or Excel files, SpendixAI can import them and keep a transaction history per category, with reports you can filter and group. That is worth considering once your formulas outgrow the time you spend maintaining them.
One more use for the counts: a category with a high count and a low average, such as tea, parking or small UPI payments to local shops, is easy to underestimate because no single payment feels large. Sorting your category list by count, not by total, shows which habits you repeat most often.
Next step
Pick your three most frequent categories, build the grid above for the last three months, and look at average ticket size next to the count. A rising count with a falling ticket usually means many small, easy-to-forget payments.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

