COUNTIF: count the rows you actually mean
Counting can be deceptive. The figure might look right while a stray space, wildcard character or blank cell changes which records qualify. COUNTIF is useful when you can state the rule precisely.
The wildcard changes the question from equal to Open to starts with Open. That may be useful for grouped workflow statuses, but it can also hide inconsistent labels. COUNTIF is not case-sensitive: OPEN and Open belong to the same comparison. Use a validation list if the allowed statuses are fixed.
| Formula | Expected result / interpretation |
|---|---|
| =COUNTIF(A2:A6,”Open”) | 2 exact text matches |
| =COUNTIF(A2:A6,”Open*”) | 4 prefix matches, including the trailing-space value |
| =COUNTIF(A2:A6,”Closed”) | 1 |
| Cell | Status |
|---|---|
| A2 | Open |
| A3 | Closed |
| A4 | Open |
| A5 | Open – awaiting stock |
| A6 | Open␠ (trailing space) |
Enter the five statuses in A2:A6. The final value contains a trailing ordinary space.
Count statuses without accidentally widening the rule
Formula syntax
=COUNTIF(range, criteria)
What goes into it
range
Cells to test.
criteria
Condition that determines which cells are counted.
A quick example first
=COUNTIF(C:C,"Complete")
Counts cells in C equal to Complete.
=COUNTIF(B2:B100,">=70")
Counts values at least 70.
Before you copy this into a real sheet
To count the literal text A* rather than a pattern, use the criterion “A~*”. Put a changing criterion in a cell and refer to it directly. For a numeric threshold stored in C2 use “>=”&C2 rather than the text “>=C2”. If the same row must satisfy both status and region, use COUNTIFS with ranges of equal size; multiplying two independent COUNTIF totals does not give the intersection.
Useful habits
- Use wildcard characters only when pattern matching is intentional.
- Join comparison operators to cell references with &, such as “>=”&E2.
- Use COUNTIFS for more than one condition.
Where people get caught out
- Putting the criteria range and criteria in the wrong order.
- Expecting COUNTIF to apply multiple independent conditions.
Other functions worth a look
A detail worth remembering
Keep in mind that an asterisk and question mark are wildcards in criteria. If you are counting literal product codes, check how those characters should be escaped.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.