SUMIF adds values whose corresponding cells meet one condition. It can test the same range it sums, or use a separate criteria column.
Keep the criteria and sum ranges aligned so each condition applies to the intended amount. For several conditions that must all hold, use SUMIFS.
SUMIF function syntax
=SUMIF(range, criterion, [sum_range])- range: the cells checked against the condition.
- criterion: a label, comparison, number, or cell reference.
- sum_range: corresponding cells to add; omitted means add the tested range itself.
The Google SUMIF reference describes the function arguments.
Example data
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Region | Sales | Product |
|---|---|---|
| East | 200 | Pro Camera |
| West | 120 | Mouse |
| east | 300 | Pro Keyboard |
| North | 80 | Cable |
| East | 100 | Webcam Pro |
| West | 0 | Cable |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Sum sales for one region
The matching East records contain 200, 300, and 100. SUMIF adds them to 600; the lowercase east record also matches.
=SUMIF(A2:A7,"East",B2:B7)Result: 600
Sum numbers above a threshold
Without a separate sum range, SUMIF tests and adds the sales values themselves. Only amounts strictly greater than 100 are included.
=SUMIF(B2:B7,">100")Result: 620
The operator is joined to B5 with &, so this threshold follows the value in that cell.
=SUMIF(B2:B7,">"&B5)Result: 720
Sum values using a text pattern
The pattern *Pro* matches Pro anywhere in the product name.
=SUMIF(C2:C7,"*Pro*",B2:B7)Result: 600
Pro* matches only names beginning with Pro, so Webcam Pro is excluded.
=SUMIF(C2:C7,"Pro*",B2:B7)Result: 500
Exclude a category or reference a criterion cell
The <> comparison adds sales for regions other than East.
=SUMIF(A2:A7,"<>East",B2:B7)Result: 200
A2 supplies the region criterion directly. Do not place quotation marks around a reference when you want its cell value.
=SUMIF(A2:A7,A2,B2:B7)Result: 600
Understand a zero result
No region matches Missing, so SUMIF returns 0.
=SUMIF(A2:A7,"Missing",B2:B7)Result: 0
Apply several conditions
SUMIFS adds East sales above 100. Its sum range comes first, unlike SUMIF’s optional sum range at the end.
=SUMIFS(B2:B7,A2:A7,"East",B2:B7,">100")Result: 500
Other Google Sheets articles you may also like