SUMIF vs SUMIFS: one condition or many
Both add up only the rows that match. SUMIF takes one condition; SUMIFS takes as many as you need. The argument order is different, which trips people up.
| SUMIF | SUMIFS | |
|---|---|---|
| Conditions | One | One or more (up to 127) |
| Sum range position | Last argument | First argument |
| Logic between conditions | — | All must be true (AND) |
| Available in | Every version | Every version since 2007 |
Side by side
SUMIF
=SUMIF(B2:B9,"West",F2:F9) → 354
SUMIFS
=SUMIFS(F2:F9,B2:B9,"West",D2:D9,">3") → 294
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Which should you use?
Use SUMIFS by default: it handles one condition too, and keeping one argument order in your head avoids mistakes.