SUMIF
Adds up numbers only in the rows that match a condition: revenue for one city, expenses for one category.
SUMIF(range, criteria, [sum_range])- [ ]optional part
- range
- Where to check the condition, for example cities in A2:A20.
- criteria
- The condition itself: text, a number or a comparison in quotes.
- sum_range
- What to add up. If omitted, the condition range itself is added up.Optional
Return value
A number: the sum of the cells that matched the condition. If none matched, the function returns 0.
Examples
Revenue for one city
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
City
Amount, $
City
Revenue, $
Boston
7200
Boston
Denver
3400
Denver
Boston
2600
Austin
8100
Denver
4300
Boston
5400
Sheet1
Details
SUMIF checks the condition in one range and adds up numbers from another range of the same length. A row gets into the sum if the condition is met.
The condition goes in quotes: "Boston", ">5000", "<>Austin". To take a threshold from a cell, join it with the & sign: ">"&F1.
Common mistakes
- The order of arguments: first where to check, then the condition, then what to add up. In SUMIFS the sum range comes first.
- Ranges of different lengths, or without $ signs when you drag the formula: some rows drop out of the sum.
- The condition ">E1" in quotes is just text. To take a threshold from a cell, write ">"&E1.