Excel functions / Maths & statistics
SUMIF
Adds up the values that meet one condition.
Syntax
=SUMIF(range, criteria, [sum_range])Arguments
range | The cells to test. |
|---|---|
criteria | The condition, such as "East", ">100" or a cell reference. |
sum_range | Optional. The cells to add. Leave it out to add the tested cells themselves. |
How it works
SUMIF answers questions such as "what are the total sales for the East region?" It checks each cell in the range and adds the matching values.
Conditions with comparison signs go in quotes: ">100". To compare against a cell, join them with &: ">"&E1.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | 750 | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: 750. Adds the sales for the two East rows: 500 + 250.
Tips
- The range and sum_range should be the same size.
- Criteria can use wildcards: "*tea" matches anything ending in tea.
Practise SUMIF
Try it yourself in a real spreadsheet, with instant feedback on every answer.