How to Use SUMIFS in Excel to Total Values by Category and Month

thienkrik - small shop — Fri, 13 Nov 2026 08:44:21 +0000

If you keep a list of income and expenses in Excel, adding everything up by hand quickly becomes slow and error-prone. SUMIFS solves that problem: it adds only the numbers that match the conditions you set, such as a category, a month, or both.

In this tutorial, you will learn the syntax of SUMIFS, work through a small example, and avoid the most common mistakes beginners make.

What SUMIFS does

SUMIFS adds values in one column when the matching rows meet one or more conditions.

The syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

sum_range: the numbers you want to add.
criteria_range1: the column Excel checks first.
criteria1: the condition that column must meet.

You can add more pairs of criteria ranges and criteria after that. Every condition must be true for a row to be included.

SUMIFS is different from SUMIF, which accepts only one condition. When you need two or more conditions, use SUMIFS.

Example data

Suppose your data is in cells A1:C7:

Date	Category	Amount
2026-11-02	Food	12
2026-11-05	Transport	8
2026-11-10	Food	15
2026-11-18	Food	9
2026-12-01	Food	20
2026-12-03	Transport	7

Column A is the date, column B is the category, and column C is the amount.

Step 1: Total one category

To add all Food amounts, use this formula:

=SUMIFS(C2:C7, B2:B7, "Food")

Excel checks each row in B2:B7. When the category is Food, it adds the matching amount from C2:C7. The result is 56 (12 + 15 + 9 + 20), because all four Food rows are included when there is no date filter.

Step 2: Total one category in one month

To add only Food amounts in November 2026, add two date conditions:

=SUMIFS(C2:C7, B2:B7, "Food", A2:A7, ">=2026-11-01", A2:A7, "<=2026-11-30")

Now Excel checks three things for each row: the category must be Food, the date must be on or after 1 November 2026, and the date must be on or before 30 November 2026. The result is 36 (12 + 15 + 9). The Food row dated 1 December, worth 20, is excluded because it falls outside November.

For a cleaner formula, put the start and end dates in their own cells instead of typing them inside the formula. For example, put 2026-11-01 in E1 and 2026-11-30 in E2:

=SUMIFS(C2:C7, B2:B7, "Food", A2:A7, ">="&E1, A2:A7, "<="&E2)

This makes the formula easier to read and lets you change the month by changing E1 and E2.

Step 3: Total a different category

Change only the criteria to total Transport in November:

=SUMIFS(C2:C7, B2:B7, "Transport", A2:A7, ">="&E1, A2:A7, "<="&E2)

The result is 8. You can copy the same pattern for any other category in your list.

Common mistakes to avoid

1. Ranges of different sizes. Every range in SUMIFS must have the same number of rows. If sum_range is C2:C7 but a criteria range is B2:B10, Excel returns an error.

2. Text that does not match exactly. "Food" and "food " are not the same to Excel. Extra spaces or different spelling will silently exclude rows, so keep category names consistent. Data validation with a drop-down list helps.

3. Dates stored as text. If a date is stored as text, date comparisons such as ">=2026-11-01" will not work as expected. Make sure the date column is formatted as a date, not as text.

4. Forgetting the quotes. Text criteria and comparison operators need double quotes, for example "Food" and ">=2026-11-01". When you refer to a cell, join it with &, as in ">="&E1.

5. Using SUMIF for two conditions. SUMIF accepts only one condition. If you need a category and a month, you need SUMIFS.

A quick practice exercise

Copy the example table above into a blank worksheet.
In a new cell, write the formula for Food in November 2026 and check that you get 36.
Change the formula to total Transport in December 2026. You should get 7.
Add a row of your own, for example a Food amount in November, and confirm the total updates.

Conclusion

SUMIFS is one of the most useful formulas in Excel for everyday totals. Start with one condition, then add a second one for the month, and keep your categories and dates consistent. Once this pattern feels natural, you can use the same idea for monthly summaries, category totals, and year-to-date totals without adding anything by hand.

DEV Community: thienkrik - small shop