Spreadsheets

SUMIFS with two criteria: category and month in Excel

Use SUMIFS in Excel to total one category in one month with a category dropdown and a month cell, then extend it to a grid of categories by month.

Editorial illustration for SUMIFS with two criteria: category and month in Excel

To total one category for one month in Excel, use SUMIFS with three conditions: the category equals your chosen cell, the date is on or after the month start, and the date is before the next month start. One formula then answers any category and month pair, because both come from cells you can change.

How do I lay out the sheet?

Say your transactions sit in A1:E500: Date in A, Narration in B, Category in C, Account in D and Amount in E. Put your question in a small box off to the side.

  • G1: Category, with the dropdown value in H1.
  • G2: Month, with the first day of the month in H2.
  • G3: Total, with the formula in H3.

How do I make the dropdown?

  1. Type your category names into a list, for example J2:J12.
  2. Click H1, then Data, Data Validation.
  3. Allow: List. Source: =$J$2:$J$12.
  4. Pick Groceries from the arrow that appears.

In H2 type a real date for the first of the month, for example 1/3/2026 in your regional date order, and format the cell as mmmm yyyy so it reads March 2026.

What is the formula?

In H3 enter this. SUMIFS wants the sum range first, then pairs of range and criteria.

  • =SUMIFS($E$2:$E$500,$C$2:$C$500,$H$1,$A$2:$A$500,">="&$H$2,$A$2:$A$500,"<"&EDATE($H$2,1))
  • $E$2:$E$500 is what gets added, $C$2:$C$500 is tested against the category, and $A$2:$A$500 is tested twice for the month window.

What does a worked example give?

Say the Groceries rows dated in March are ₹1,240, ₹860, ₹2,150 and ₹540, and one Groceries row of ₹700 is dated 2 April. For March, the formula returns 1,240 + 860 + 2,150 + 540 = ₹4,790. The April row is left out by the less-than test. Change H2 to 1 April and the same formula returns ₹700, if that is the only April row.

Category in H1Month in H2Rows countedResult
GroceriesMarch 20264₹4,790
GroceriesApril 20261₹700
FuelMarch 2026Whatever matchesChanges with the data

Can I make a whole grid from the same formula?

Yes, with mixed references. Put category names down G6:G15 and month starts across H5:S5. In H6 enter the formula below, then copy it right and down. The dollar signs let the category column and the month row stay fixed while the other part moves.

  • =SUMIFS($E$2:$E$500,$C$2:$C$500,$G6,$A$2:$A$500,">="&H$5,$A$2:$A$500,"<"&EDATE(H$5,1))

How do I show the month as text instead of a date?

If you prefer typing 2026-03 into the month cell, you can compare it with a helper column. In F2 use =TEXT(A2,"yyyy-mm") and copy it down. The formula then becomes =SUMIFS($E$2:$E$500,$C$2:$C$500,$H$1,$F$2:$F$500,$H$2), with H2 holding the text 2026-03. It is shorter, and it only needs one date test hidden in the helper column.

The trade-off is one more column to maintain. The date-window version needs no helper column and is harder to break when someone pastes new rows below the data.

Can I add a third condition, such as the account?

Yes. SUMIFS accepts up to 127 pairs of criteria range and criteria, so you can add another pair for the Account column in D. Put the account name in H4 and extend the formula by one pair: ,$D$2:$D$500,$H$4. The result is Groceries, in March, on the card only.

Each added condition narrows the result, so the answer can only stay the same or get smaller. If adding a condition makes the number jump up, something is wrong with the ranges, not the data.

How do I check the grid?

Total each column and compare with a plain monthly SUMIFS that has no category condition. If the monthly total is ₹38,000 and the category cells for that month add up to ₹36,500, then ₹1,500 sits in a category you have not listed. Find it by filtering the sheet to that month and removing the listed categories.

If you would rather not maintain formulas at all, SpendixAI has a Report builder that filters and groups spending, which covers the same question once your statements are imported.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports