Math
Counting rows that pass two tests
Definition
Counting rows that pass two labels at once, such as north and full, means giving COUNTIFS each range and its match.
Syntax
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Arguments
| Argument | Meaning |
|---|---|
criteria_range1 |
The first label column, such as the bay cells. |
criteria1 |
The first label to match, in double quotes when it is text, for example "north". |
criteria_range2 |
The second label column, such as the load cells. |
criteria2 |
The second label to match, for example "full". |
Worked example
A dock lists bay and load in A2:B5.
| A: Bay | B: Load |
|---|---|
| north | full |
| south | full |
| north | empty |
| north | full |
Formula: =COUNTIFS(A2:A5,"north",B2:B5,"full")
Result: 2. north and full appear together in rows 2 and 5.
Practice
Cells B2:B5 hold east, west, east, and east. Cells C2:C5 hold open, open, open, and open. In D2, write a formula that counts rows where B2:B5 equals east and C2:C5 equals open.
- In D2, write a COUNTIFS formula that counts how many rows have east in B2:B5 and open in C2:C5.
- D2 should show how many rows have east in B2:B5 and open in C2:C5.
Step 1 of 2
Enter =COUNTIFS(B2:B5,"east",C2:C5,"open") in D2.
east and open appear together in rows 2, 4, and 5, so the count is 3.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
D2 uses COUNTIFS.
D2 now shows 3.
The next lesson is Making a label all capitals.
Related functions
COUNTIFS is the function that applies both tests and then counts the rows that pass.
Common mistake
Leaving out the second test, as in =COUNTIFS(A2:A5,"north"), which still counts the empty north row and returns 3.