What it does
COUNTIF takes two arguments: the range to count and the condition. The condition can be a value ("West", 100), a comparison in quotes (">=10000", "<>Cancelled") or a pattern with wildcards ("KBL*"). To compare with the value in a cell, join the operator with &: ">="&E2.
COUNTIF is the quickest way to find duplicates: =COUNTIF($A$2:$A$100,A2) returns how many times each row's value appears in the list, and anything above 1 is a duplicate. =COUNTIF($A$2:A2,A2) counts how many times the value has appeared up to that row, so you can flag only the second and later occurrences.
For several conditions use COUNTIFS; to add up the matching rows, SUMIF. Special conditions: "<>" counts non-empty cells, "" empty cells and "*" cells that contain text.
Syntax
=COUNTIF(range, criteria)=EĞERSAY(aralık; ölçüt)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
range | Required | The cells to count against the condition, e.g. the region column B2:B50. |
criteria | Required | The condition: "West", ">=10000", "KBL*", "<>" or a cell (E2). Case is ignored. |
Examples
1How many orders came from each region?
The regions are in column D. The formula in E2 is filled down; the range is locked with $, so only the condition cell moves.
=COUNTIF($B$2:$B$8,D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Region | Orders | |
| 2 | S-1001 | West | West | 4 | |
| 3 | S-1002 | North | North | 2 | |
| 4 | S-1003 | West | South | 1 | |
| 5 | S-1004 | South | |||
| 6 | S-1005 | West | |||
| 7 | S-1006 | North | |||
| 8 | S-1007 | West |
- E2 (filled down to E4)
=COUNTIF($B$2:$B$8,D2)
4 orders came from the West, 2 from the North and 1 from the South.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Count invoices above an amount
The threshold is in D2. The condition joins the comparison operator with the cell: ">="&D2.
=COUNTIF(B2:B7,">="&D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Amount | Threshold | At or Above | |
| 2 | F-1001 | 4,200.00 | 10,000 | 3 | |
| 3 | F-1002 | 18,750.00 | |||
| 4 | F-1003 | 9,999.90 | |||
| 5 | F-1004 | 10,000.00 | |||
| 6 | F-1005 | 26,400.00 | |||
| 7 | F-1006 | 7,300.00 |
Including the invoice of exactly 10,000, 3 invoices meet the threshold. Change D2 and the count follows.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Flag duplicate email addresses
Column B counts how many times each address appears in the list; column C flags those that appear more than once. No rows are deleted, they just become visible.
=COUNTIF($A$2:$A$7,A2)| A | B | C | |
|---|---|---|---|
| 1 | Occurrences | Status | |
| 2 | sales@brownandsons.co.uk | 2 | Duplicate |
| 3 | info@harbourtrade.co.uk | 2 | Duplicate |
| 4 | accounts@northgate.com | 1 | Unique |
| 5 | sales@brownandsons.co.uk | 2 | Duplicate |
| 6 | support@starmarket.com | 1 | Unique |
| 7 | info@harbourtrade.co.uk | 2 | Duplicate |
- B2 (filled down to B7)
=COUNTIF($A$2:$A$7,A2) - C2 (filled down to C7)
=IF(B2>1,"Duplicate","Unique")
Brown & Sons' address appears 2 times and both rows show Duplicate; Northgate's address is Unique.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result is 0
Cause: No cell met the condition. Often the spelling differs (an extra space, another spelling) or the numbers are stored as text.
Fix: Copy the condition from the list or put it in a cell and refer to that cell; clean spaces with TRIM.
- Count too high
Cause: A
*or?in the condition was treated as a wildcard; "A*" counts everything that starts with A.Fix: To look for a real asterisk or question mark, put
~in front of it:"~*".- #VALUE!
Cause: The condition is a text longer than 255 characters.
Fix: Shorten the condition, or use SUMPRODUCT to compare long texts.
Good to know
- Put comparison conditions in quotes:
">=10000". A plain number means equal to. - Build date conditions with DATE so they do not depend on regional settings:
">="&DATE(2025,10,1).
In Nexell
Get Nexell to do it from a sentence
Add a Duplicate column that flags email addresses appearing more than once in the customer list
Convert to tableBring your list into the tool and type this. Nexell adds the Duplicate column with real Excel formulas and the preview shows which rows are flagged; no rows are deleted, they are only marked.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I use COUNTIF with more than one condition?
If every condition must hold, use COUNTIFS. If any one is enough (OR), add two COUNTIFs: =COUNTIF(B2:B50,"West")+COUNTIF(B2:B50,"South").
How do I find duplicate values in Excel?
Add =COUNTIF($A$2:$A$100,A2) next to the list and fill it down; results above 1 are duplicates. Label them with =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique").