How to use SUMIFS and COUNTIFS with two conditions
SUMIFS and COUNTIFS add or count only the rows that pass every test, such as one rep's sales to one customer. Learn the order of their parts.
Task 1SUMIFS adds the rows that pass every test3 questions
SUMIF takes one test. SUMIFS takes as many tests as you need, and adds a row only when it passes all of them.
- Range to add
- comes first in SUMIFS, such as D2:D7
- A range and its test
- written as a pair, such as C2:C7,"Asha"
- More pairs
- one pair for each extra test
Watch the order. In SUMIF the range to add comes last, and in SUMIFS it comes first. Both work the same way in Excel and Google Sheets.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Customer | Rep | Amount |
| 2 | 2101 | Quay Dental | Asha | 240.00 |
| 3 | 2102 | Hollin School | Ben | 412.50 |
| 4 | 2103 | Quay Dental | Ben | 95.00 |
| 5 | 2104 | Moss Lane Vets | Asha | 130.25 |
| 6 | 2105 | Hollin School | Asha | 320.00 |
| 7 | 2106 | Quay Dental | Asha | 75.50 |
Task 2COUNTIFS, and tests on figures3 questions
You work out a figure and sort items into the right place.