SUMIFS
Adds up numbers in the rows that match several conditions at once: revenue for one city in one sales channel.
SUMIFS(sum_range, criteria_range1, criteria1, ...)- sum_range
- What to add up, for example order amounts in C2:C8.
- criteria_range1
- Where to check the first condition, for example cities in A2:A8.
- criteria1
- The first condition. Then you add a second range and a second condition the same way.
Return value
A number: the sum over the rows that matched all conditions at once. If there are no such rows, the function returns 0.
Examples
Revenue by city and channel
fx
A
B
C
D
E
F
G
H
1
2
3
4
5
6
7
8
9
10
City
Channel
Amount, $
City
Channel
Revenue, $
Boston
Online
5200
Boston
Online
Denver
Store
3100
Boston
Store
Boston
Store
4700
Denver
Online
Boston
Online
2800
Denver
Online
6400
Boston
Store
1900
Denver
Online
2300
Sheet1
Details
SUMIFS takes the range to add up first, and then pairs: where to check and what condition. A row gets into the sum only if all conditions are met.
Conditions are written the same way as in SUMIF: text in quotes, a comparison in quotes like ">5000", a threshold from a cell joined with &: ">"&F1. All ranges must be the same length.
Common mistakes
- The order of arguments is mixed up with SUMIF: in SUMIFS the sum range comes first.
- Ranges of different lengths: the formula returns a #VALUE! error.
- An “or” condition (Boston or Denver) can’t be set in one SUMIFS: all its conditions work as “and”. Add two formulas together.