SUMIF is one of the most useful formulas in Excel and Google Sheets — and one of the most misunderstood. If you've ever needed to add up only certain values based on a condition, SUMIF is what you need.
⚡ Quick Answer: =SUMIF(range, criteria, sum_range) — adds values in sum_range where range matches criteria.
What Does SUMIF Actually Do?
SUMIF adds numbers in a range — but only the ones meeting a specific condition you define. Instead of SUM which adds everything, SUMIF is selective.
Think of it as: "Add up the sales — but only the ones from the North region."
The Syntax Explained
- range: The cells to check against your condition (e.g., the Region column)
- criteria: What to match (e.g., "North", ">100", or a cell reference)
- sum_range (optional): Cells to actually add up. If omitted, SUMIF sums the range itself.
Example 1 — Sales by Region
You have sales data with Column A = Region and Column B = Sales Amount. To total North region sales:
This checks column A for "North" and adds matching values from column B.
Example 2 — Values Above a Threshold
Sums all values in column B that are greater than 1000. Use comparison operators inside quotes.
Example 3 — Wildcard Matching
The * wildcard matches any characters. This sums sales from any region starting with "N" — North, Northeast, Northwest.
Example 4 — Cell Reference as Criteria
Put your criteria in cell E1 and reference it. Change E1 to "South" and totals update automatically — no formula edits needed.
SUMIF vs SUMIFS — Key Difference
SUMIF handles one condition. SUMIFS handles multiple conditions — but the argument order is different:
⚠️ In SUMIFS, the sum_range comes FIRST — opposite to SUMIF. This trips up even experienced Excel users.
5 Common SUMIF Mistakes
Mistake 1: Forgetting Quotes Around Text
Mistake 2: Wrong Comparison Syntax
Mistake 3: Mismatched Range Sizes
If range is A2:A100 (99 cells), sum_range must also be 99 cells. Mismatch = silent wrong results.
Mistake 4: Hidden Spaces in Data
"North" and "North " (trailing space) won't match. Use TRIM to clean your data first.
Mistake 5: Using SUMIF When You Need SUMIFS
SUMIF can only check ONE condition. For multiple conditions (region = North AND month = January), switch to SUMIFS.
Real-World Use Cases
- Budget tracking:
=SUMIF(B:B,"Groceries",C:C)— total grocery spending - Sales reports:
=SUMIF(A:A,"Q1",B:B)— Q1 revenue only - HR payroll:
=SUMIF(D:D,"Full-time",E:E)— full-time salaries - Inventory:
=SUMIF(C:C,"<10",D:D)— value of low-stock items
Use =SUMIF(A:A, "<>", B:B) to sum values where column A is NOT empty. The <> means "not equal to blank."
SUMIF in Google Sheets
SUMIF works identically in Google Sheets with the same syntax. For even more power, Google Sheets has the unique QUERY function: =QUERY(A:B,"select sum(B) where A='North'")