Math & aggregation
How to Use SUMIF in Excel (Sum with a Condition)
SUMIF adds up the numbers in a range that meet a single condition: total sales for one region, the sum of all "Paid" invoices, every value over 100.
This guide covers text, numeric, wildcard, and date criteria, plus when to graduate to SUMIFS for multiple conditions.
SUMIF syntax
=SUMIF(range, criteria, [sum_range]) | Argument | Required | What it does |
|---|---|---|
range | Required | The cells to test against the criteria. |
criteria | Required | The condition: "Paid", ">100", or a cell reference like E1. |
sum_range | Optional | The cells to actually add. If omitted, SUMIF sums the range itself. |
Note: For more than one condition, use SUMIFS. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Sum by a text label
=SUMIF(A:A, "Paid", B:B) Tests column A for "Paid" and adds the matching values from column B. Text criteria go in double quotes.
2. Sum values over a threshold
=SUMIF(B:B, ">100") With a numeric comparison you can drop the sum_range. SUMIF then adds the range it is testing. The operator goes inside the quotes.
3. Criteria from a cell + a date
=SUMIF(A:A, ">="&DATE(2026,1,1), B:B) When the operator meets a cell reference or function, join them with &: ">="&DATE(...). The same trick works for ">="&E1 to read a threshold from a cell.
How to write SUMIF step by step
- 1
Click the result cell and type =SUMIF(
- 2
Select the range that holds the values to test (e.g. the status column), then a comma.
- 3
Type the condition in quotes ("Paid" or ">100"), then a comma.
- 4
Select the range of numbers to add (the sum_range). Skip this if it is the same as the test range.
- 5
Close the bracket ) and press Enter. For multiple conditions, use SUMIFS instead.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Result is 0 | The criteria does not match, usually a type mismatch (number stored as text) or stray spaces. | Check that the test column and criteria are the same type; clean text with TRIM(). |
#VALUE! | range and sum_range are different shapes. | Make range and sum_range the same size and orientation. |
Operator ignored | A comparison like >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">"&E1. |
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF adds values that meet one condition. SUMIFS handles multiple conditions and puts the sum range first: =SUMIFS(sum_range, range1, criteria1, range2, criteria2).
How do I use SUMIF with multiple criteria?
Use SUMIFS instead. For example, =SUMIFS(C:C, B:B, "West", A:A, "January") sums column C where region is West and month is January.
Can SUMIF use wildcards?
Yes. Use * for any number of characters and ? for a single character, e.g. =SUMIF(A:A, "*West*", B:B) sums rows whose label contains "West".
Does SUMIF work with dates?
Yes. Combine the operator with DATE(): =SUMIF(A:A, ">="&DATE(2026,1,1), B:B) sums everything from that date onward.