Math
Counting cells that hold one word
Definition
Counting cells that hold one word means giving COUNTIF a list of words and the label to match.
Syntax
=COUNTIF(range, criteria)
Arguments
| Argument | Meaning |
|---|---|
range |
The block of cells to scan. |
criteria |
The word to count. Wrap it in double quotes, for example "in". |
Worked example
A gate log records in or out in B2:B5.
| A: Pass | B: Status |
|---|---|
| 1 | in |
| 2 | out |
| 3 | in |
| 4 | in |
Formula: =COUNTIF(B2:B5,"in")
Result: 3. in appears in B2, B4, and B5, so three cells match.
Practice
Cells A2:A5 hold red, blue, red, and green. In B2, write a formula that counts how many cells equal red.
- In B2, write a COUNTIF formula that counts how many cells in A2:A5 equal red.
- B2 should show how many cells in A2:A5 equal red.
Step 1 of 2
Enter =COUNTIF(A2:A5,"red") in B2.
red appears in A2 and A4, 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…
B2 uses COUNTIF.
B2 now shows 2.
The next lesson is Counting numbers above a cutoff.
Related functions
COUNTIF is the function that counts cells that hold that word.
Common mistake
Leaving the quotes off the word, as in =COUNTIF(B2:B5,in), so the formula looks for a name called in instead of the word.