COUNTIF counts cells matching a condition. Here is the complete guide — syntax, 6 real examples, COUNTIFS, common mistakes and FAQ.
COUNTIF counts the number of cells in a range that meet a condition you set. It answers questions like: how many sales were above target? How many employees are in one department? How many responses were "Yes"?
It is one of the most useful formulas for data analysis — giving you instant counts from large datasets without filtering or scrolling through rows manually.
=COUNTIF(range, criteria)| Argument | Description |
|---|---|
| range required | The range of cells to count. This is the column you want to check. |
| criteria required | The condition a cell must meet to be counted. Text goes in quotes; numbers and cell references do not. |
=COUNTIF(A2:A100, "North")Counts every cell in column A that contains exactly "North". Case insensitive — "north" and "NORTH" both count.
=COUNTIF(B2:B100, ">1000")Counts how many values in column B exceed 1,000. Use comparison operators inside quotes for numeric criteria.
=COUNTIF(A2:A100, E1)E1 contains the value to count. Change E1 and the count updates automatically — making this a dynamic, reusable formula.
=COUNTIF(A2:A100, "<>"")<>"" means "not equal to empty". This counts every cell that has any content — same result as COUNTA.
=COUNTIF(A2:A100, "*manager*")Counts any cell containing the word "manager" anywhere — "Sales Manager", "HR Manager", "Account Manager" all match.
=COUNTIF($A$2:$A$100, A2)For each row, counts how many times that row's value appears in the entire column. Copy this formula down — any row showing 2 or more is a duplicate.
COUNTIFS counts rows where every condition is true simultaneously.
=COUNTIFS(range1, criteria1, range2, criteria2, ...)=COUNTIFS(A2:A100, "Female", B2:B100, "London")Both conditions must be true for a row to be counted. Add more range/criteria pairs for additional filters.
| Formula | What it counts |
|---|---|
| COUNT | Cells containing numbers only. Ignores text and empty cells. |
| COUNTA | All non-empty cells — numbers, text, dates, everything. |
| COUNTIF | Cells meeting a specific condition you define. |
| COUNTIFS | Cells meeting multiple conditions simultaneously. |
| COUNTBLANK | Empty cells only. |
=COUNTIF(A:A,North) errors because North has no quotes. Text criteria need quotes: "North". Similarly ">1000" needs quotes but cell references like E1 do not.
To combine a comparison operator with a cell reference, use & to join them: =COUNTIF(B:B,">"&E1). You cannot write =COUNTIF(B:B,>E1).
COUNTIF is not case sensitive. "North", "NORTH" and "north" all match the same cells. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
ExcelPro has COUNTIF and COUNTIFS exercises across every track — free to start, no download needed.
Try COUNTIF exercises →