How to Use COUNTIF in Excel (with Examples)
COUNTIF counts only the cells that meet a condition. The syntax is =COUNTIF(range, criteria) — like counting how many sales came from one region. Here is exactly how it works, plus wildcards, counting duplicates, and COUNTIFS.
COUNTIF is the function you reach for when you want to count only the cells that meet a condition — not every cell, just the ones that match. How many sales came from the North region? How many scores were above 80? How many times does one name appear in a list? COUNTIF answers each with a single, readable formula.
The COUNTIF syntax, explained
=COUNTIF(range, criteria)| Part | Meaning |
|---|---|
| range | The cells to check against your condition |
| criteria | The condition to match, like "North" or ">80" |
A real example
Say column A holds regions in the range A2:A100. To count how many rows are from the North region, you write:
=COUNTIF(A2:A100, "North")This checks every cell in A2:A100 and returns how many equal "North". Wrap text criteria in quotation marks. You can also test numbers with an operator — =COUNTIF(B2:B100, ">80") counts every value greater than 80. Put the operator inside the quotes, together with the number.
Partial matches with wildcards
COUNTIF understands two wildcards in text criteria: an asterisk (*) for any number of characters and a question mark (?) for a single character. To count every product code that starts with "AB":
=COUNTIF(A2:A100, "AB*")A lone asterisk, =COUNTIF(A2:A100, "*"), counts every cell that contains text — a quick way to count non-blank labels while ignoring numbers and empty cells.
Counting duplicates with COUNTIF
One of the most useful jobs for COUNTIF is spotting repeats. In a helper column next to your data, count how many times each value appears in the whole column:
=COUNTIF(A:A, A2)Any result above 1 means the value is a duplicate. Copy the formula down and you can instantly see which entries repeat. To flag them visually instead, use conditional formatting with a COUNTIF rule, and when you are ready to clean the list, see how to remove duplicates in Excel.
More than one condition: COUNTIFS
When you need two or more conditions — say the North region AND sales over 100 — use COUNTIFS. It takes range and criteria in pairs:
=COUNTIFS(A2:A100, "North", B2:B100, ">100")COUNTIF pairs naturally with other tools. Add up the matching values instead of counting them with SUMIF, build extra logic around a count with an IF function, or do the same task in Google Sheets with SUMIF in Google Sheets.
Frequently asked questions
What is the difference between COUNT, COUNTA, and COUNTIF?
COUNT counts only cells that contain numbers. COUNTA counts every non-empty cell, numbers and text alike. COUNTIF counts cells that meet a condition you set. Use COUNTIF when you need to count by a rule rather than count everything.
How do I count duplicates in Excel with COUNTIF?
In a helper column, use =COUNTIF(A:A, A2) and copy it down. Any result greater than 1 means that value appears more than once. To count only the extra copies, subtract 1 from each result.
Can COUNTIF use two conditions?
No — COUNTIF handles a single condition. For two or more conditions that must all be true, use COUNTIFS, which takes range and criteria in pairs, like =COUNTIFS(A2:A100, "North", B2:B100, ">100").
Why is my COUNTIF returning 0?
Usually the criteria does not match the data — a trailing space, slightly different text, or numbers stored as text. Check that "North" in your formula exactly matches "North" in the cells, and that the range covers the right rows.