Math
Adding amounts that pass two tests
Definition
Adding the numbers in a column for rows that pass two labels at once, such as east and open, means giving SUMIFS the amounts first and then each test.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Arguments
| Argument | Meaning |
|---|---|
sum_range |
The column of amounts to add. |
criteria_range1 |
The first label column, such as the region cells. |
criteria1 |
The first label to match, in double quotes when it is text, for example "east". |
criteria_range2 |
The second label column, such as the status cells. |
criteria2 |
The second label to match, for example "open". |
Worked example
A clinic logs wing, day, and bed counts in A2:C5.
| A: Wing | B: Day | C: Beds |
|---|---|---|
| west | sat | 5 |
| west | sun | 8 |
| east | sat | 3 |
| west | sat | 7 |
Formula: =SUMIFS(C2:C5,A2:A5,"west",B2:B5,"sat")
Result: 12. west and sat appear together in rows 2 and 5, so 5 + 7 equals 12.
Practice
Cells B2:B5 hold east, west, east, and east. Cells C2:C5 hold open, open, shut, and open. Cells D2:D5 hold 12, 9, 4, and 8. In E2, write a formula that totals D2:D5 where B2:B5 equals east and C2:C5 equals open.
- In E2, write a SUMIFS formula that adds D2:D5 where B2:B5 equals east and C2:C5 equals open.
- E2 should show the total of D2:D5 where B2:B5 equals east and C2:C5 equals open.
Related functions
SUMIFS is the function that applies both tests and then adds the amounts that pass.
Common mistake
Leaving out the second test, as in =SUMIFS(D2:D5,B2:B5,"east"), which still adds the shut east row and overstates the total.