Math
How many cells match one test
Definition
Counting matches means asking COUNTIF how many cells in a range equal a given value.
Syntax
=COUNTIF(range, criteria)
Arguments
| Argument | Meaning |
|---|---|
range |
The block of cells to scan. |
criteria |
The match to count. Wrap text in double quotes, for example "oak". |
Worked example
A park list records tree types in B2:B5.
| A: Plot | B: Tree |
|---|---|
| Plot 1 | oak |
| Plot 2 | pine |
| Plot 3 | oak |
| Plot 4 | birch |
Formula: =COUNTIF(B2:B5,"oak")
Result: 2. oak appears in B2 and B4, so two cells match.
Practice
Cells C2:C5 hold yes, no, yes, and yes. In D2, write a formula that counts how many cells equal yes.
- In D2, write a COUNTIF formula that counts how many cells in C2:C5 equal yes.
- D2 should show how many cells in C2:C5 equal yes.
Step 1 of 2
Enter =COUNTIF(C2:C5,"yes") in D2.
yes appears in C2, C4, and C5, 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 COUNTIF.
D2 now shows 3.
The next lesson is Adding amounts that match one test.
Related functions
COUNTIF is the function that counts matches, while COUNT only reports how many cells hold numbers.
Common mistake
Leaving the quotes off a text test, as in =COUNTIF(B2:B5,oak), so Excel looks for a name called oak instead of the word.