Conditional sums and counts
SUMIF function playground
Adds the values that meet one condition.
The playground runs in your browser. Nothing you type is sent anywhere.
How SUMIF works
SUMIF walks down the range one row at a time. Where the row meets the condition, it adds the matching cell from sum_range; other rows are skipped.
Write comparisons in quotes: ">1500", "<>East". To compare against a cell, join them with &: ">"&I1. Text matching ignores upper and lower case, and * and ? work as wildcards.
Syntax
=SUMIF(range, criteria, [sum_range])| Argument | What it means |
|---|---|
rangerequired | The cells to test, such as the Region column. |
criteriarequired | The condition: a value like "East", a comparison like ">1500", or a cell such as I1. |
sum_rangeoptional | The cells to add. Leave it out to add the tested cells themselves. |
Examples
| Formula | What it does | Result |
|---|---|---|
=SUMIF(B2:B11,I1,F2:F11) | Revenue for the region typed in I1. Change I1 to West or North. | $8,700.00 |
=SUMIF(B2:B11,"East",F2:F11) | The same, with the region written in the formula. | $8,700.00 |
=SUMIF(F2:F11,">=1500") | Add only sales of 1,500 or more (no sum_range needed). | $13,900.00 |
=SUMIF(D2:D11,"Lap*",E2:E11) | Units of any product that starts with "Lap". | 10 |
Common mistakes
Ranges of different sizesMake range and sum_range the same height, such as B2:B11 and F2:F11. A shorter sum_range gives misleading results.
Comparison without quotes>1500 must be written as ">1500" inside the formula.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.