Skip to content

Excel function · Statistics

Excel COUNTIF function

COUNTIF returns how many cells in a range meet your condition: how many orders came from the West, how many invoices exceeded 10,000, how often an email address appears in a list.

In Turkish Excel: EĞERSAY

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

English Excel=COUNTIF(range, criteria)
Turkish Excel=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

ArgumentRequiredDescription
rangeRequiredThe cells to count against the condition, e.g. the region column B2:B50.
criteriaRequiredThe 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.

E2fx=COUNTIF($B$2:$B$8,D2)
How many orders came from each region?: example table
ABCDE
1OrderRegionRegionOrders
2S-1001WestWest4
3S-1002NorthNorth2
4S-1003WestSouth1
5S-1004South
6S-1005West
7S-1006North
8S-1007West
  • 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.

E2fx=COUNTIF(B2:B7,">="&D2)
Count invoices above an amount: example table
ABCDE
1InvoiceAmountThresholdAt or Above
2F-10014,200.0010,0003
3F-100218,750.00
4F-10039,999.90
5F-100410,000.00
6F-100526,400.00
7F-10067,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.

B2fx=COUNTIF($A$2:$A$7,A2)
Flag duplicate email addresses: example table
ABC
1EmailOccurrencesStatus
2sales@brownandsons.co.uk2Duplicate
3info@harbourtrade.co.uk2Duplicate
4accounts@northgate.com1Unique
5sales@brownandsons.co.uk2Duplicate
6support@starmarket.com1Unique
7info@harbourtrade.co.uk2Duplicate
  • 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

Type this in Nexell

Add a Duplicate column that flags email addresses appearing more than once in the customer list

Convert to table

Bring 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").

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free