Monthly totals by category with SUMIFS and EDATE
Build a category-by-month grid from a list of transactions with one SUMIFS formula you can fill across and down.
The problem
You have a list of dated transactions and want totals per category per month.
Sample data
Sheet Data (A Date, B Category, C Amount):
| Date | Category | Amount |
|---|---|---|
| 46296 (2026-10-01) | Food | 10 |
| 46297 | Food | 50 |
| 46300 | Food | 30 |
| 46305 | Rent | 1000 |
| 46326 (2026-10-31) | Food | 5 |
| 46330 (2026-11-04) | Food | 40 |
Sheet Grid: column A is the category, column B is the first day of a month (a real date).
Formula
=SUMIFS(Data!$C$2:$C$7,Data!$B$2:$B$7,A2,Data!$A$2:$A$7,">="&B2,Data!$A$2:$A$7,"<"&EDATE(B2,1))
Results: Food in October 95 (the first and last day are included), Rent October 1000, Food November 40, Rent November 0.
Watch out
Dates must be real dates, not text. Test with =ISNUMBER(cell).
Excel vs Sheets
Same in both.
Tested on sample data by the Cellwise checker (log CW-PL-20261021-64). AI-made: the text, voice and visuals are generated by AI.
Checked before publishing: a check.