“How much did we sell in the East region in March?” is a question with two conditions, and SUMIF — which handles only one — can’t answer it. SUMIFS can. It adds up only the rows that match all of your conditions, and it takes as many conditions as you need.
What SUMIFS does
SUMIFS adds the numbers in one column, but only for rows where every condition you list is true. One condition, three conditions, ten conditions — the shape of the formula stays the same.
The syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range — the numbers to add. This comes first in SUMIFS.
- criteria_range1 — the column to test against the first condition.
- criteria1 — the condition, for example
"East"or">100". - Then repeat in pairs: another range, another condition.
Warning: the older SUMIF puts the test range first and the sum range last. SUMIFS reverses that order. Mixing them up is the most common SUMIFS mistake, and Excel won’t warn you — you’ll just get a wrong number.
Step-by-step example
You have a sales table: column A is the region, column B is the product, column C is the amount.
- Click the cell for your answer.
- Type
=SUMIFS($C$2:$C$100,$A$2:$A$100,"East",$B$2:$B$100,"Notebook"). - Press Enter. You get total Notebook sales in the East region only.
To make the formula reusable, put the conditions in cells instead of typing them into the formula: =SUMIFS($C$2:$C$100,$A$2:$A$100,$E$2,$B$2:$B$100,$E$3) where E2 holds the region and E3 holds the product. Change the cells, the answer updates.
Worked example
| Region | Product | Amount |
|---|---|---|
| East | Notebook | $120 |
| East | Pen set | $85 |
| West | Notebook | $200 |
| East | Notebook | $95 |
| West | Pen set | $60 |
=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Notebook") returns $215 ($120 + $95). The West Notebook sale of $200 is excluded, and so is the East Pen set sale.
Useful condition patterns
- Numbers:
">100"(over 100),"<=50"(50 or less),"<>0"(not zero). - Dates:
">=2026-01-01"combined with"<2026-02-01"gives you January only. Both conditions test the same date column. - Partial text:
"*book*"matches anything containing “book”. The asterisk is a wildcard. - Cell reference with an operator:
">"&E2— the quotes and the & join the operator to the cell value. - Blank / not blank:
"<>"matches non-empty cells.
Common errors
#VALUE! — ranges are different sizes. Every criteria range must have exactly the same number of rows as the sum range. If the sum range is C2:C100, every other range must also be 2:100.
Zero when you expect a number. Usually a criteria problem: the text in your formula doesn’t exactly match the text in the cells. “East ” with a trailing space is not “East”. Numbers stored as text also fail numeric comparisons like “>100” — convert them first.
Dates don’t match. A date stored as text (“2026-01-05” typed as text) won’t match a date comparison. Check the cell alignment: real dates align right, text aligns left.
SUMIF vs SUMIFS argument order. If you learned SUMIF first, your fingers will put the test range first. In SUMIFS the sum range comes first. Double-check the order whenever the answer looks wrong.
Practice file
Download the SUMIFS practice file (.xlsx) — a 40-row sales table, six ready-made SUMIFS formulas (region, product, date range, wildcard, and two combined conditions), plus a blank sheet to build your own.
Google Sheets
SUMIFS works identically in Google Sheets, including wildcards and date comparisons. The same file opens in Sheets and the formulas keep working.
All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example figures are illustrative, not market data.