How much did we sell in the East region? The total of everything is easy — SUM does that. The total for one group needs SUMIF, which adds a row’s amount only when that row matches your rule. It is the fastest way to answer “how much for X” without filtering and re-adding by hand.
What SUMIF does
SUMIF tests one column against your rule and adds the matching rows from another column. With two arguments it adds the tested column itself; with three it adds a separate sum column. That two-versus-three argument difference is the single most common SUMIF mistake, so it is worth learning properly once. Once it clicks, the same pattern carries straight over to SUMIFS, COUNTIF, COUNTIFS, and AVERAGEIF — the whole family works the same way.
The syntax
=SUMIF(range, criteria, [sum_range])
- range — the column to test, for example Region.
- criteria — the rule, for example “East” or “>100”.
- sum_range — optional: the column to add. If you leave it out, Excel adds the tested column itself.
Step-by-step example
Add up East-region sales from this list.
- Click the cell where the answer should go.
- Type
=SUMIF(A2:A9,"East",D2:D9)— A is the region tested, “East” is the rule, D is the amount added. - Press Enter. The East total appears.
- To total by product instead, test the product column:
=SUMIF(B2:B9,"Notebook",D2:D9). - If the numbers to add are in the same column you test, drop the third argument:
=SUMIF(D2:D9,">100")adds the amounts over $100.
Worked example
| Region | Product | Amount |
|---|---|---|
| East | Notebook | $120 |
| East | Pen set | $85 |
| West | Notebook | $200 |
| East | Notebook | $95 |
=SUMIF(A2:A5,"East",D2:D5) returns $300 — $120 + $85 + $95. The West row is tested, fails, and contributes nothing.
Common errors
0 when there should be a total. Usually the sum range and the test range are different sizes, or the criteria has a typo or an extra space. Check that the test column really contains “East” exactly as typed.
The answer is the count, not the sum. You passed two arguments when you needed three — =SUMIF(A2:A9,"East") adds the Region column, which holds text, so the result is 0. Always pass the sum range when the numbers live in another column.
Dates do not match. A date rule like ">2026-01-31" needs real dates in the tested column. Text dates never match a date comparison.
Wildcards add the wrong rows. "*book*" matches Notebook and storybook alike. For an exact product name, use the name with no asterisks.
Tip
For two or more conditions, use SUMIFS instead — same idea, pairs of range and rule. SUMIF stays useful for the one-condition case because it is shorter to type. And if the numbers to add are already in the column you are testing, remember you can drop the third argument entirely and let SUMIF add the tested column itself.
SUMIF with dates: this month’s total
Dates work like any other criteria. To total this month’s sales, test the date column twice inside SUMIFS (or once in SUMIF with a built-in month check): =SUMIFS(D2:D100,C2:C100,">="&DATE(2026,1,1),C2:C100,"<"&DATE(2026,2,1)) adds January only. The pattern is always the same — “>=” the first of the month and “<" the first of next month — and it works for weeks and quarters too, just by changing the two boundary dates. If the month should follow today's date automatically, build the boundaries with DATE(YEAR(TODAY()),MONTH(TODAY()),1) and EOMONTH(TODAY(),0)+1 instead of typing them.
Practice file
Download the SUMIF practice file (.xlsx) — it contains a sales list and a summary sheet with SUMIF answers already filled in, plus a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports SUMIF with the same syntax. Sheets also accepts a sum range that is a different size from the test range and silently uses the top-left corner — Excel does the same, so keep both ranges the same size in either app.
Example prices and figures are illustrative, not market data.