COUNTIF is one of those Excel functions that sounds simple but turns out to be useful in dozens of situations. At its most basic, it counts how many cells in a range match a condition you set. That might be counting how many times a customer name appears, how many invoices are over a certain amount, or how many cells are blank. Once you know the syntax, you will find yourself reaching for it regularly.
What COUNTIF Does
COUNTIF scans a range of cells and counts how many of them match a criteria you specify. Unlike COUNT, which just counts numbers, COUNTIF lets you set the rule for what qualifies as a match. It returns a single number — the count of matching cells.
The Two Arguments
The formula structure is straightforward:
=COUNTIF(range, criteria)- range — the cells you want to check (for example, A2:A100)
- criteria — the condition a cell must meet to be counted
For example, to count how many times “London” appears in column A:
=COUNTIF(A2:A100, "London")Counting Text
Exact Match
Wrap the text in double quotes. Matching is not case-sensitive, so “london”, “London”, and “LONDON” all count the same:
=COUNTIF(A2:A100, "London")You can also point to a cell instead of typing the text directly, which makes the formula easier to reuse:
=COUNTIF(A2:A100, D2)Wildcard Matching
Use an asterisk (*) to match any sequence of characters. This is useful when you want partial matches:
=COUNTIF(A2:A100, "North*")This counts cells containing “North”, “North East”, “Northampton” — anything starting with “North”. Place the asterisk at both ends to match anywhere in the text:
=COUNTIF(A2:A100, "*Ltd*")A question mark (?) matches exactly one character. "Jon?" would match “John” or “Jone” but not “Jonathan”.
Counting Numbers with Operators
Use comparison operators inside double quotes to count numbers based on a threshold:
=COUNTIF(B2:B100, ">500")This counts all cells in column B with a value over 500. You can use: >, <, >=, <=, =, <> (not equal to).
To reference a threshold stored in a cell, combine the operator (in quotes) with the cell reference using an ampersand:
=COUNTIF(B2:B100, ">="&E1)This counts values greater than or equal to whatever number is in E1. Change E1 and the count updates automatically.
Counting Blank and Non-Blank Cells
To count cells that are empty:
=COUNTIF(A2:A100, "")To count cells that are not empty:
=COUNTIF(A2:A100, "<>")Note: COUNTIF counts truly empty cells and cells containing empty strings slightly differently in some versions of Excel. For a reliable count of non-blank cells, COUNTA (covered below) is often cleaner.
Using COUNTIF to Find Duplicates
One of the most practical uses of COUNTIF is spotting duplicate entries. Add a helper column next to your data and use a formula like this (assuming your data is in column A):
=COUNTIF($A$2:$A$100, A2)This tells you how many times the value in A2 appears across the whole column. If the result is 1, it is unique. If it is 2 or more, it is a duplicate.
To flag duplicates with a label, wrap it in an IF:
=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique")Copy this formula down the column and you can instantly see which entries appear more than once. You can then filter or sort by this column to review or remove them.
COUNTIFS for Multiple Conditions
When you need to count rows that meet two or more conditions simultaneously, use COUNTIFS:
=COUNTIFS(range1, criteria1, range2, criteria2)All conditions must be true for a row to be counted. For example, to count customers in “London” with an order value over 500:
=COUNTIFS(A2:A100, "London", B2:B100, ">500")You can keep adding criteria pairs as needed. The ranges must all be the same size and shape.
COUNTA vs COUNT vs COUNTIF
These three functions are often confused. Here is when to use each:
- COUNT — counts cells that contain numbers only. Ignores text, blanks, and errors.
- COUNTA — counts cells that are not empty. Works on any data type — numbers, text, dates.
- COUNTIF — counts cells that meet a specific condition you define.
Use COUNT when you want to know how many numeric entries you have. Use COUNTA when you want to count all filled cells regardless of type. Use COUNTIF when you need to count based on a rule.
Practical Examples
How Many Orders Are From a Specific Region
Column A contains region names, and you want to know how many orders came from the South:
=COUNTIF(A2:A500, "South")How Many Invoices Are Overdue
Column B contains due dates. To count invoices with a date before today:
=COUNTIF(B2:B200, "<"&TODAY())How Many Items Are Still Outstanding
Column C contains a status field. Count anything that is not marked “Complete”:
=COUNTIF(C2:C200, "<>Complete")Count a Name Across a List
You have a log where sales rep names appear in column D. To see how many entries belong to “Sarah”:
=COUNTIF(D2:D1000, "Sarah")Or with the name in a cell:
=COUNTIF(D2:D1000, G2)Common Mistakes
Forgetting quotes around text or operators — Text criteria and operators like “>100” must be in double quotes. If you write =COUNTIF(A2:A100, >100) without quotes, Excel will return an error.
Extra spaces in data — If a cell contains ” London” (with a leading space) instead of “London”, COUNTIF will not count it as a match. Use TRIM to clean your data if counts seem lower than expected.
Ranges of different sizes in COUNTIFS — All ranges in a COUNTIFS formula must be the same number of rows. Mixing A2:A100 with B2:B50 will give you a #VALUE! error.
Wildcards not working with numbers — Wildcards only work with text. If you try "1*" to match all numbers starting with 1, it will not work as expected on numeric cells.
