COUNTIF counts cells in one range that meet one condition. You can count how often a city appears, how many sales exceed a threshold, or how many cells are blank.
COUNTIF syntax
The function uses COUNTIF(range, criteria). The range contains the cells to check, and criteria determines which cells count.
Text and operator criteria such as >1000 belong in quotes. To compare against a threshold stored in another cell, join the operator and cell reference with &.
Count records by city
Use the formula below to count cells containing London in B2:B100.
The comparison is not case-sensitive, but leading or trailing spaces can split what should be one category.
How to do it
- Choose the range to count.
- Enter one criterion.
- Validate the result against a filtered count.
=COUNTIF(B2:B100,"London")One criterion versus two in the same table
A holds city, B order status, and C amount. In the four rows below Istanbul appears three times, while Istanbul and Completed occur together twice. COUNTIF answers the first question; COUNTIFS tests both conditions on the same row.
The returned Istanbul order counts for the city but not for completed orders. Define which records belong in the answer before writing the formula, then compare it with a filtered view of the table.
| Row | City | Status | Amount |
|---|---|---|---|
| 2 | Istanbul | Completed | 1,200 |
| 3 | Ankara | Completed | 800 |
| 4 | Istanbul | Returned | 400 |
| 5 | Istanbul | Completed | 500 |
=COUNTIFS(A2:A5,"Istanbul",B2:B5,"Completed")Align COUNTIFS criteria ranges
A2:A5 and B2:B5 cover the same four rows. If one range ends at row 6, the row pairing is invalid and Excel returns an error. To use a criterion from a cell, replace the quoted text with a reference such as E2.
This page now includes the former COUNTIFS guide's multi-criteria example. SheetCommand can show a total row count, but it does not create this conditional count or the formula automatically.
=COUNTIF(A2:A5,"Istanbul")COUNTIF criteria and limits
Use COUNTIFS instead when several conditions must be true. In text criteria, * represents any sequence of characters and ? represents one character.
Inconsistent spelling and extra spaces can distort category counts. Cleaning the source column first makes the result more reliable.
Frequently asked questions
Can COUNTIF use several conditions?
One COUNTIF handles one condition. Use COUNTIFS for multiple conditions.
Does SheetCommand add COUNTIF formulas?
No. This guide explains the formula; the tool can summarize and clean the source file before you use it.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF tests one condition; COUNTIFS requires several conditions on the same row. The example results are 3 and 2 respectively.
Sources and further reading
SheetCommand
Clean categories before counting
Remove duplicate and completely blank rows and inspect the data summary before using COUNTIF.