Math
COUNTIFS
Definition
COUNTIFS returns how many rows meet every test you give it.
Syntax
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Arguments
| Argument | Meaning |
|---|---|
criteria_range1 |
The first set of cells to test, such as A2:A5. |
criteria1 |
The required value in that first set. Wrap text in double quotes. |
criteria_range2 |
The second set of cells to test. |
criteria2 |
What the second set of cells must equal, or a comparison such as ">2". |
Worked example
A lumber yard logs wood types and plank counts. Types are in A2:A5 and plank counts are in B2:B5.
| A: Type | B: Planks |
|---|---|
| oak | 4 |
| pine | 5 |
| oak | 1 |
| oak | 3 |
Formula: =COUNTIFS(A2:A5,"oak",B2:B5,">2")
Result: 2. Oak with more than 2 planks sits in rows 2 and 5, so two rows match.
Practice
Cells A2:A5 hold red, blue, red, and red. Cells B2:B5 hold 5, 5, 3, and 5. In C2, write a formula that counts rows where A2:A5 equals red and B2:B5 equals 5.
- In C2, write a COUNTIFS formula that counts how many rows have red in A2:A5 and 5 in B2:B5.
- C2 should show how many rows have red in A2:A5 and 5 in B2:B5.
Step 1 of 2
Enter =COUNTIFS(A2:A5,"red",B2:B5,5) in C2.
red and 5 appear together in rows 2 and 5, so the count is 2.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses COUNTIFS.
C2 now shows 2.
Related functions
COUNTIF counts cells that pass one test. COUNT counts every numeric cell. COUNTIFS adds a second test before it counts.
Common mistake
Leaving the comparison unquoted, as in =COUNTIFS(A2:A5,"oak",B2:B5,>2): the >2 must sit in double quotes.