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.

The Name Box displays D2, and cell D2 in the Summary sheet's Total column is selected
The Name Box displays D2. The green selection border is on row 2 of the Total column, across from the first Materials entry.

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 Name Box shows D2 and the formula bar shows the start of the SUMIFS formula
The Name Box displays D2, and the formula bar on the right starts with SUMIFS. Click the image to view the full formula.

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$8 is the sum range. All three detail ranges use $ to lock rows 2 through 8.
  • ">="&A2 includes the first day of the current month. The & joins the comparison symbol with the date in A2.
  • "<"&B2 includes dates before the first day of the next month.
  • Finally, C2 specifies 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.

Category and Total columns showing four rows with amounts 80, 12, 90, and 18 respectively
From top to bottom: April Materials, April Transport, May Materials, May Transport. Click the image to view the full date columns.

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.

Cue AI button in the Home toolbar and the open Cue AI panel
Open Cue AI from the Home toolbar, and the chat panel appears on the left.
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.

Category and Total columns showing four rows with amounts 80, 12, 90, and 18 respectively
After reopening the AI copy, the four rows are April Materials 80, April Transport 12, May Materials 90, and May Transport 18. Click the image to view the full date columns.

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.

April 1 materials amount shows 60 in the test copy of Expenses
In the test copy, cell C3 in Expenses displays 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.

Category and Total columns showing four rows with amounts 95, 12, 90, and 18 respectively
The rows follow the same month and category order. The first total updates from 80 to 95. Click the image to see the full date columns.

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
Three columns in Expenses for Date, Category, and Amount, along with seven repair expense entries
Expenses contains records across two years, including expenses falling on the first and last days of the month.
Summary sheet showing two months and two category combinations ready for totaling
Summary reserves one row for each month and category combination.

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.

Download Cue Office for free