How to Use SUMIFS by Month and Category in Excel
Open your Excel workbook (.xlsx) in Cue Office and use SUMIFS to total expenses by month and category. For April materials, the formula adds an expense only if it falls in April and its category is Materials.
Enter the SUMIFS formula on the Summary sheet
On Summary, enter the first day of the month in column A, the first day of the next month in column B, and the category in column C. Then select D2, the first cell under Total.
On Expenses, columns A, B, and C contain the date, category, and amount. Copy the four formulas below and paste them with D2 selected in the grid.
=SUMIFS(Expenses!$C$2:$C$8,Expenses!$A$2:$A$8,">="&A2,Expenses!$A$2:$A$8,"<"&B2,Expenses!$B$2:$B$8,C2) =SUMIFS(Expenses!$C$2:$C$8,Expenses!$A$2:$A$8,">="&A3,Expenses!$A$2:$A$8,"<"&B3,Expenses!$B$2:$B$8,C3) =SUMIFS(Expenses!$C$2:$C$8,Expenses!$A$2:$A$8,">="&A4,Expenses!$A$2:$A$8,"<"&B4,Expenses!$B$2:$B$8,C4) =SUMIFS(Expenses!$C$2:$C$8,Expenses!$A$2:$A$8,">="&A5,Expenses!$A$2:$A$8,"<"&B5,Expenses!$B$2:$B$8,C5)
The first argument of SUMIFS is the range of amounts to add. The subsequent arguments come in pairs: first specify which column to check, then specify the criteria that column must meet.
Expenses!$C$2:$C$8is the sum range. All three detail ranges use$to lock rows 2 through 8.">="&A2includes the first day of the current month. The&joins the comparison symbol with the date in A2."<"&B2includes dates before the first day of the next month.- Finally,
C2specifies the category. Each formula only changes the row numbers for A, B, and C to reference the criteria for the current summary row.
When you use your own records, make the date, category, and amount ranges start and end on the same rows. If you add expenses, extend all three ranges. Use date values for the transaction dates and month boundaries, and numbers for the amounts.
Check month boundaries and category totals
Start with the materials total for April 2026. The 45 on April 1 plus the 35 on April 30 gives 80. Check a few expenses in other months, years, and categories to verify the criteria.
- Dates: March 31 falls before the start date, and May 1 belongs to May. The April 2025 record also falls outside the date range.
- Categories: The 12 on April 10 belongs to Transport and is grouped into April transport expenses.
Save and reopen the workbook, then check D2:D5 on Summary. The four rows should show April materials 80, April transport 12, May materials 90, and May transport 18.
Have Cue AI write the summary formulas
If you already have the months and categories listed, Cue AI can fill in the formulas together. Open Summary in a copy of the original workbook, select Cue AI on the Home tab, and send this prompt.
Can you fill in D2:D5 on Summary with a formula for each row’s total expenses for that month and category? Use the dates in the first two columns to include the first day of the month and leave the first day of the next month for the next total. Please leave the existing dates, categories, and expense records as they are.
After Cue AI writes the formulas, close the panel, save the workbook, and reopen it. Select D2 and check the date and category criteria against the manual formula. Cue AI puts the category criterion before the date criteria, and the four totals are still 80, 12, 90, and 18. On Expenses, check that the April 1 materials expense still reads 45.
To see whether the formulas recalculate, save a separate test copy. In that copy, enter =45+15 in Expenses!C3 to change the April 1 materials expense from 45 to 60.
Switch back to Summary in the test copy. The April materials total should update from 80 to 95. April transport, May materials, and May transport should still show 12, 90, and 18.
Practice with community repair expenses
Show the complete example
A community repair team wants to review materials and transport expenses separately by month. In the same workbook, create two sheets named Expenses and Summary, then paste the headers and data below each sheet name starting at cell A1. Confirm that date columns contain valid dates and amount columns contain numbers, then write the formulas starting in cell D2 of Summary.
Expenses Date Category Amount 2026-03-31 Materials 70 2026-04-01 Materials 45 2026-04-10 Transport 12 2026-04-30 Materials 35 2026-05-01 Materials 90 2026-05-12 Transport 18 2025-04-10 Materials 100 Summary Month start Next month Category Total 2026-04-01 2026-05-01 Materials 2026-04-01 2026-05-01 Transport 2026-05-01 2026-06-01 Materials 2026-05-01 2026-06-01 Transport
Reconcile expenses against reimbursement records
Enter the reimbursement totals beside your summary, matching each one to its month and category. Then ask Cue AI to write a formula that subtracts the sheet total from the reimbursement total. If the reimbursement records show 95 for April materials and the sheet shows 80, the difference should be 15. Check the receipts for April materials and see whether the April 1 expense was entered as 45 instead of 60.
Use Cue Office to open PDF (.pdf) receipts and edit the Excel (.xlsx) expense sheet for free. Once you find the source of the difference, you can record the reason for the correction in a Word (.docx) reimbursement note or update the materials total in a PowerPoint (.pptx) monthly report.