How to Use SUMIF in Excel (and SUMIFS)
SUMIF adds up the cells that meet one condition. The syntax is =SUMIF(range, criteria, sum_range) — so =SUMIF(A2:A100, "North", B2:B100) totals column B for every row where column A says “North”. When you need more than one condition, use SUMIFS.
The three arguments
| Argument | What it is | Example |
|---|---|---|
range | The cells to check | A2:A100 |
criteria | What to match | "North" |
sum_range | The cells to add up | B2:B100 |
If you leave out sum_range, Excel sums the range itself — handy when the condition and the numbers are in the same column.
Example: total sales by region
Say column A holds regions and column B holds sales:
=SUMIF(A2:A100, "North", B2:B100)That returns the total of every North sale. Change "North" to "South" and you have the next region — build a small table and fill it down.
Using operators and cell references
Criteria can be more than plain text:
| To match | Write the criteria as |
|---|---|
| Exactly “North” | "North" |
| Greater than 500 | ">500" |
| Not blank | "<>" |
| A value in another cell | ">"&E2 |
| Text ending in “west” | "*west" |
Referencing a cell (">"&E2) is the trick that makes a summary table driven entirely by inputs.
SUMIFS: several conditions at once
SUMIFS puts sum_range first, then pairs each condition with its range:
=SUMIFS(B2:B100, A2:A100, "North", C2:C100, "Q1")This totals column B where column A is “North” and column C is “Q1”. Add more range/criteria pairs for more conditions — up to 127.
Note the order swap: SUMIF = range first, SUMIFS = sum_range first. Mixing them up is the most common SUMIF error.
Common mistakes
| Problem | Fix |
|---|---|
| Wrong total | Check the ranges are the same size; A2:A100 needs B2:B100, not B2:B50 |
#VALUE! | A range is text where numbers were expected, or sizes mismatch |
| Criteria ignored | Look for stray spaces, and make sure it’s in quotes |
0 instead of a total | The criteria never matches — test it in a spare cell |
| Only part summed | The range ends before the data does |
SUMIF vs. its cousins
| Formula | Use it for |
|---|---|
| SUM | Add everything |
| SUMIF | Add where one condition is true |
| SUMIFS | Add where several conditions are true |
| COUNTIF / COUNTIFS | Count matching rows instead of adding |
| AVERAGEIF / AVERAGEIFS | Average matching rows |
Once your totals are correct, make a graph in Excel to show them, or feed the figures into a pivot table for a deeper breakdown. If you’re building a lookup instead of a total, see how to use VLOOKUP in Excel.
FAQ
What does SUMIF do in Excel?
It adds up the cells in one range that match a single condition you specify. =SUMIF(A:A,"North",B:B) totals B wherever A is “North”.
What’s the difference between SUMIF and SUMIFS?
SUMIF takes one condition; SUMIFS takes several. SUMIFS also lists the sum range first, then each criteria range and value.
How do I SUMIF with a “greater than” condition?
Put the operator inside the quotes and concatenate a cell: =SUMIF(B2:B100, ">500") or =SUMIF(B2:B100, ">"&E2).
Why is my SUMIF returning 0?
The criteria isn’t matching — often a trailing space, a number stored as text, or a case/format difference. Test the exact value in a spare cell first.
Can I use SUMIF across two sheets?
Yes — reference the other sheet in the range, e.g. =SUMIF(Sheet2!A:A,"North",Sheet2!B:B).
Related guides
Google Sheets Formulas for Beginners (10 You Need to Know)
The 10 essential Google Sheets formulas every beginner needs — SUM, AVERAGE, COUNT, IF, and more, with working examples.
How to Add Checkboxes in Google Sheets (Tick Boxes)
Add checkboxes in Google Sheets with Insert → Checkbox, set custom checked/unchecked values, and count ticks with COUNTIF or a progress bar.
How to Create a Drop-Down List in Excel (Data Validation)
Add a drop-down list in Excel with Data Validation — pick from a typed list or a range on another sheet, and fix the errors that stop it working.