COUNTIF in Excel: Syntax, Examples and Errors
Updated 2026-10-13 · Free · Works in Excel & Google Sheets
Syntax
=COUNTIF(range, criteria)Quick answer
COUNTIF counts the cells in a range that meet one condition: =COUNTIF($D$2:$D$500, ">100") returns how many cells hold a number above 100. It also matches text, dates and wildcard patterns, and is not case-sensitive. A result of 0 usually means numbers stored as text or missing quotes around the criteria.
Syntax
=COUNTIF(range, criteria)| Argument | What it means |
|---|---|
| range | The cells to evaluate: a single column or row, e.g. $D$2:$D$500. |
| criteria | The condition a cell must meet: a number, text in double quotes, a comparison like ">100", or a cell reference such as D2. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =COUNTIF($D$2:$D$500; ">100"). This is standard in countries where the comma is the decimal separator.
Basic examples
=COUNTIF($D$2:$D$500, ">100")Counts the sales values above 100. Comparison operators (>, >=, <, <>) always go inside the quoted string, glued to the number.
=COUNTIF($C$2:$C$500, "New York")Counts the cells that contain exactly "New York". Text criteria always need double quotes, and matching is case-insensitive: "new york" counts the same.
=COUNTIF($B$2:$B$500, ">2026-01-01")Counts the dates after January 1, 2026. The comparison stays inside the quotes; the cells must hold real dates, not text that looks like one.
=COUNTIF($A$2:$A$500, "*@gmail.com")Counts the emails ending in @gmail.com. The * wildcard matches any number of characters, ? matches exactly one, and ~ escapes a literal * or ? in the text.
A classic duplicate check rides on COUNTIF: =COUNTIF($A$2:$A$500, A2)>1 returns TRUE for every value that appears more than once. Copy it next to the column and filter the TRUEs to see all duplicates.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #NAME? | COUNTIF is misspelled — or your Excel is set to another language, where the function has a localized name (CONTAR.SI, NB.SI, ZÄHLENWENN, CONT.SE). | Type =COUNT( and let autocomplete complete the name, or open the file in the matching language version of Excel. |
| The result is always 0 | Numbers stored as text, invisible trailing spaces, or criteria written without its quotes (">100" must include the quotes). | Convert text-numbers, clean the spaces, and keep the criteria in double quotes. |
| Text never matches | The cells carry invisible spaces: to COUNTIF, "NYC " and "NYC" are different strings. Case never matters — spaces do. | COUNTIF ignores case but not spaces: trim the whole column before counting. |
| Dates are never counted | The "dates" are text imported from another system, so ">2026-01-01" never matches. | Convert the column to real dates first — a date criteria only works on true date values. |
Trailing spaces are the quiet killer here too: the count comes back short with no error message. Clean the whole column in one click before counting.
COUNTIF vs. COUNTIFS
| COUNTIF | COUNTIFS | |
|---|---|---|
| Conditions per formula | One | Two to 127 |
| Combines conditions with AND | No | Yes |
| Different range per condition | — | Yes, as long as the ranges are the same size |
| Available in | Every Excel version (and Google Sheets) | Excel 2007+ (and Google Sheets) |
=COUNTIFS($C$2:$C$500, "New York", $D$2:$D$500, ">100")Move to COUNTIFS the moment you need two conditions at once — sales above 100 in New York, for example. Plain COUNT counts numbers only and COUNTA counts non-empty cells; COUNTIF and COUNTIFS cover everything conditional.
COUNTIF in Google Sheets
Google Sheets supports COUNTIF with the same arguments as Excel, so the formulas above can be pasted over unchanged.
- Comparison operators, wildcards and case-insensitive matching behave identically in Sheets.
- In regional settings that use a comma as the decimal separator (much of Europe and Latin America), type semicolons between arguments: =COUNTIF($D$2:$D$500; ">100"). Excel behaves the same way.
- Google Sheets also offers COUNTIFS with the same rules — multi-condition counts work the same in both apps.
- Sheets adds COUNTUNIQUE, which counts distinct values directly; in Excel that needs a SUMPRODUCT trick or a dedicated tool.
Limitations and pro tips
- COUNTIF cannot compare two ranges row by row (count where D is greater than E) — that needs SUMPRODUCT(($D$2:$D$500>$E$2:$E$500)*1).
- One condition only. For OR logic, add two COUNTIFs: =COUNTIF(range, ">100")+COUNTIF(range, "<0").
- It cannot count by cell color — filter by color and read the status bar, or add a helper column.
- Lock ranges with $ signs ($D$2:$D$500) so the range does not slide sideways when you copy the formula down.
- Counting distinct values with COUNTIF alone is awkward and slow on big ranges — extract unique values instead.
Counting duplicates is only the first step: once you have spotted them, remove them in one pass. The free tools below do the cleanup without formulas.
Related Tools
Related Functions
Frequently Asked Questions
What does COUNTIF do in Excel?
It counts the cells in a range that meet a single condition — numbers above a threshold, exact text, dates before a deadline or wildcard patterns: =COUNTIF($D$2:$D$500, ">100"). Matching is case-insensitive, and one formula handles exactly one condition.
How do I count cells greater than a number?
Put the operator inside quoted criteria: =COUNTIF($D$2:$D$500, ">100"). The quotes are required — without them Excel reads >100 as an invalid formula. Use >= for or-equal, and & to glue in a cell reference: ">"&D1.
Why is my COUNTIF returning 0?
Almost always a data problem, not a formula problem: numbers stored as text, invisible trailing spaces, or criteria missing its quotes. Convert text-numbers with VALUE(), trim the spaces, and check that "New York" really is wrapped in double quotes.
Is COUNTIF case-sensitive?
No — COUNTIF treats "NYC", "nyc" and "Nyc" as the same value. If you truly need a case-sensitive count, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(range, "NYC")).
How do I use wildcards in COUNTIF?
* matches any number of characters and ? matches exactly one: =COUNTIF($A$2:$A$500, "*@gmail.com") counts Gmail addresses. To match a literal asterisk or question mark, escape it with a tilde: ~* or ~?.
When should I use COUNTIFS instead of COUNTIF?
The moment you have two or more conditions that must all be true: =COUNTIFS($C$2:$C$500, "New York", $D$2:$D$500, ">100"). COUNTIF handles one condition; COUNTIFS handles 2 to 127 and works in Excel 2007+ and Google Sheets.
Does COUNTIF work in Google Sheets?
Yes — same name, same arguments, same wildcards and case-insensitive matching. Only the argument separator changes in comma-decimal regional settings, where you type semicolons: =COUNTIF($D$2:$D$500; ">100").
Ready to try it on your own file?
Open Remove Duplicates from Excel — It's Free