Function guides · Math
SUMIF function
Adds the values in a range that meet one condition.
Syntax
=SUMIF(range, criteria, [sum_range])
| Argument | What it is |
|---|---|
range | The cells to test against the condition. |
criteria | The condition, such as "West" or ">100". |
sum_range | Optional. The cells to add. If omitted, range itself is added. |
What SUMIF does
SUMIF adds up the values that sit next to cells meeting a condition. It answers questions of the form “how much came from X”.
You hand it three things: the range to check, the condition, and the range to add. When you are checking and adding the same cells, you can skip the third.
Matching is not case sensitive and supports the wildcards * and ?. Comparisons such as “>300” and “<>East” go inside quotes. One condition is the limit. Anything more detailed needs SUMIFS.
SUMIF examples
1. Total sales for one region
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 1345 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 275 |
Excel checks column A for "West" and adds the matching amounts from column B: 450 + 620 + 275. The test range and the sum range are different columns of the same height.
2. Total of the values above a threshold
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 2280 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 275 |
When the thing you are testing is the thing you are adding, leave out the third argument. This adds only the sales above 300. Notice that the comparison lives inside quotes.
3. Criteria from a cell
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sale | Region | ||
| 2 | West | 450 | East | 1020 | |
| 3 | East | 900 | |||
| 4 | West | 620 | |||
| 5 | North | 310 | |||
| 6 | East | 120 |
Typing the region into the formula locks it in. Pointing at a cell lets one formula serve a whole summary table. D2 holds "East", and the formula reads it.
This is how a regional summary is built: one SUMIF with absolute ranges, filled down next to a list of region names.
Common SUMIF mistakes
- Swapping the argument order with SUMIFS. SUMIF is range, criteria, sum_range. SUMIFS puts sum_range first. Copying one pattern into the other returns 0 or #VALUE!.
- Sum range of a different size. SUMIF tolerates it, but anchors the sum range to the first cell and resizes it to match. The result is quietly wrong. Keep both ranges the same height.
- Missing quotes on a comparison.
=SUMIF(B2:B6,>300)is a syntax error. Write ">300". - Using SUMIF for two conditions. It takes only one. Use SUMIFS when the total depends on two or more fields.
- Typos in criteria that return 0. No error appears when nothing matches, only 0. If a total is zero, check spelling and trailing spaces in the data.
SUMIF on Excel job tests
A conditional total reads like "total the sales for the West region" or "add all invoices over 1,000". Before typing, decide which column holds the label and which holds the numbers. Most mistakes are the two ranges swapped.
If the task has a summary table with one row per region, write the formula in the first row using a cell reference for the region and dollar signs on the ranges, then fill down. Typing "West" into the formula means editing every row.
A total of zero almost always means nothing matched. Compare the criterion with the data character by character, looking for spaces and spelling.
Related functions
SUMIF questions
- What is the difference between SUMIF and SUMIFS?
- SUMIF supports one condition and puts the sum range last. SUMIFS supports many conditions and puts the sum range first.
- Can SUMIF use wildcards?
- Yes.
=SUMIF(A2:A6,"W*",B2:B6)adds rows whose label starts with W. - How do I sum with a date condition?
- Use a comparison with a date, such as
">="&DATE(2026,3,1), in the criteria. For a range of dates you need SUMIFS. - Why does SUMIF return 0?
- Nothing matched the criteria. Check for spelling differences, trailing spaces, and numbers stored as text.
Practice SUMIF
The lesson SUMIF: total what matches (module 8 of Everyday Excel) has checked SUMIF exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample