Syntax
COUNTIF(range, criteria)
| Parameter | Description |
|---|---|
| range | Parameter of the COUNTIF function. |
| criteria | Parameter of the COUNTIF function. |
Examples
| A | B | C | |
|---|---|---|---|
| 1 | Order | Status | Amount |
| 2 | 1001 | Shipped | 250 |
| 3 | 1002 | Pending | 180 |
| 4 | 1003 | Shipped | 420 |
| 5 | 1004 | Cancelled | 90 |
| 6 | 1005 | Shipped | 310 |
Count cells with a specific value
Formula
=COUNTIF(B2:B100, "Shipped")
Returns: 34 — the number of rows where column B contains "Shipped"
Count values greater than a number
Formula
=COUNTIF(C2:C100, ">500")
Returns: 12 — how many sales amounts exceed $500
Count cells matching a partial string
Formula
=COUNTIF(A2:A100, "*Smith*")
Returns: 5 — counts all names containing "Smith" anywhere in the text
Common Errors
#VALUE!
The criteria argument is malformed. Make sure comparison operators are inside quotes: ">500" not >500.
Returns 0 unexpectedly
Check for extra spaces in your data or criteria. Use TRIM on your data or add wildcards: "*value*".
Tips
Use cell references in criteria
Instead of hardcoding: =COUNTIF(B:B, E1) where E1 contains the value to match. This makes the formula dynamic.
Count blanks and non-blanks
Count empty cells: =COUNTIF(A:A, ""). Count non-empty: =COUNTIF(A:A, "<>").
Case insensitive
COUNTIF is case-insensitive by default. "shipped" and "Shipped" are counted the same way.
Try COUNTIF in Viztab
Import your data and use COUNTIF with 370+ other formulas. No signup required.
Open Viztab