Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Statistical

COUNTIF

AANTAL.ALS in Dutch Excel

Counts how many cells in a range meet one condition, such as equal to a value, above a number or starting with certain letters.

Difficulty rating

Last updated: 15 October 2026

See the worked examples

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Entry no.	Journal	Journal	MEM
2	25-0401	VRK	Entries	33=COUNTIF(B2:B8, E1)=COUNTIF(B2:B8; E1)
3	25-0402	MEM
4	25-0403	INK
5	25-0404	MEM
6	25-0405	BNK
7	25-0406	VRK
8	25-0407	MEM

Result: 3

=COUNTIF(B2:B8, E1)

=COUNTIF(B2:B8; E1)

What COUNTIF does and when to use it

What it does: it counts the cells in a range that meet one criterion, for example the journal lines that are manual MEM entries.

When to use it: for tallies beside a list, to check whether a value occurs at all, or to flag duplicates (a count above 1). The condition can use wildcards and comparisons. For two or more conditions use COUNTIFS.

How Excel evaluates it

Excel reads criteria first and splits it into an operator and a value: ">=30" becomes 'greater than or equal to' and 30; a criterion without an operator means 'equal to'. If the value looks like a number, Excel compares numerically; otherwise it compares text, ignoring case and treating * and ? as wildcards.

It then tests each cell in range and adds 1 for every cell that passes. Cells whose type does not fit the comparison, such as text in a numeric test, fail it.

Watch out: COUNTIF reads a criterion that consists of digits only as a number, so references or codes longer than 15 digits are compared on their first 15 digits and can be counted as equal.

Mental model

A tally counter at a door with one rule on a card. Each cell walks past, the doorkeeper checks the card, and the counter clicks only for those that match.

Terms used on this page: criteria, wildcard, operator, range, cell

Syntax

Official COUNTIF documentation

COUNTIF(range, criteria)

COUNTIF(range; criteria)

Arguments
Argument	Type	Meaning
`range`	Required	The cells to test.
`criteria`	Required	The condition a cell must meet to be counted.

Return value:A whole number: how many cells in range meet criteria

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Count manual memorial entries in the journal

You received the XAF auditfile of Veldhuis Installatie BV as a sheet with one row per journal entry. Before journal entry testing you want to know how many entries were posted manually in the memorial journal MEM. You want a cell where you type a journal code and see the count.

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Entry no.	Journal	Journal	MEM
2	25-0401	VRK	Entries	33=COUNTIF(B2:B8, E1)=COUNTIF(B2:B8; E1)
3	25-0402	MEM
4	25-0403	INK
5	25-0404	MEM
6	25-0405	BNK
7	25-0406	VRK
8	25-0407	MEM
9

Formula cell (E2)Input

Formula in E2

=COUNTIF(B2:B8, E1)

=COUNTIF(B2:B8; E1)

Result: 33

Step by step

Excel reads E1 and finds MEM. There is no operator, so the criterion means 'equal to MEM'.
It tests each cell of B2:B8: B2 no (VRK), B3 yes, B4 no (INK), B5 yes, B6 no (BNK), B7 no (VRK), B8 yes.
Three cells pass, B3, B5 and B8, so COUNTIF returns 3.
Change E1 to VRK and the result becomes 2 (B2 and B7), without touching the formula.

Watch out: An export that stores MEM with a trailing space is not counted, and you understate the number of manual entries to test. LEN on the cell shows the extra character.

Count journal lines per relation type by code prefix

In the sales ledger export the relation code starts with the type: D1 for customers (debiteuren), C2 for suppliers (crediteuren). One code was typed in lower case, and an intercompany relation has the code DIC-01, which also starts with D. You want a count of lines per prefix in a small summary next to the list, copied down.

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Invoice no.	Relation	Prefix	Count
2	F-2501	D1001	D1	44=COUNTIF($B$2:$B$8, D2 & "")=COUNTIF($B$2:$B$8; D2 & "")
3	F-2502	C2001	C2
4	F-2503	D1002
5	F-2504	d1004
6	F-2505	DIC-01
7	F-2506	C2002
8	F-2507	D1005
9

Formula cell (E2)InputNote: The formula is in E2 and copied down to E3.

Formula in E2

=COUNTIF($B$2:$B$8, D2 & "*")

=COUNTIF($B$2:$B$8; D2 & "*")

Result: 44

Step by step

Excel builds the criterion: D2 holds D1, and & "*" adds a wildcard, giving the text D1*: 'starts with D1, followed by anything'.
It tests B2:B8: D1001 yes; C2001 no; D1002 yes; d1004 yes, because the comparison ignores case; DIC-01 no, because its second character is I, not 1; C2002 no; D1005 yes.
Four cells pass, so COUNTIF returns 4.
Without & "*" the criterion would be D1 on its own, meaning 'exactly D1'. No relation code is exactly that, so the result would be 0.
Copied to E3, the criterion becomes C2* and the count is 2 (C2001 and C2002). The $ signs kept the list at B2:B8.

Watch out: Wildcards only match text. If some relation codes were plain numbers, a pattern such as 10* would not count them.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Joining a cell with a wildcard turns a fixed prefix into a reusable pattern, so one formula serves every row of the summary. Using D1 instead of D as the prefix keeps the intercompany code DIC-01 out; the pattern D* would count it as a customer.

More to watch out for

A prefix cell with a trailing space, D1 , builds D1 * and counts nothing.

Variations

=COUNTIF($B$2:$B$8, "*" & D2 & "*")

=COUNTIF($B$2:$B$8; "*" & D2 & "*")

Contains instead of starts with: a wildcard on both sides finds the text anywhere in the cell.

Advanced

Number of different debtors for confirmation letters

You selected the open invoices at year end for debtor confirmations and want to know how many letters to send: one per debtor, not one per invoice. Several debtors have more than one invoice, and one relation code was typed in lower case. A classic construction combines COUNTIF with SUMPRODUCT to count each debtor once.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Invoice no.	Relation	Different debtors
2	V-2601	D1001	44=SUMPRODUCT(1 / COUNTIF(B2:B9, B2:B9))=SUMPRODUCT(1 / COUNTIF(B2:B9; B2:B9))
3	V-2602	D1002
4	V-2603	D1001
5	V-2604	D1003
6	V-2605	D1002
7	V-2606	D1004
8	V-2607	d1001
9	V-2608	D1003
10

Formula cell (D2)Input

Formula in D2

=SUMPRODUCT(1 / COUNTIF(B2:B9, B2:B9))

=SUMPRODUCT(1 / COUNTIF(B2:B9; B2:B9))

Result: 44

Step by step

The criteria argument is the range B2:B9 itself, so COUNTIF runs eight times, once per relation code, and returns eight counts.
D1001 occurs 3 times, counting d1001 because case is ignored; D1002 2 times; D1003 2 times; D1004 once. The array is {3; 2; 3; 2; 2; 1; 3; 2}.
1 / that array gives each row a share: {1/3; 1/2; 1/3; 1/2; 1/2; 1; 1/3; 1/2}.
Each debtor's shares add up to exactly 1: D1001 1/3 + 1/3 + 1/3 = 1, D1002 1/2 + 1/2 = 1, D1003 1/2 + 1/2 = 1, D1004 1.
SUMPRODUCT adds all eight shares: 1 + 1 + 1 + 1 = 4 different debtors, so you send 4 letters.
Change B8 to D1005. D1001 now occurs twice, so its two shares are 1/2 each, D1005 gets 1, and the result becomes 5.

Watch out: A blank cell in B2:B9 makes COUNTIF return 0 for that row, and 1 / 0 turns the whole result into #DIV/0!. Use SUMPRODUCT((B2:B9 <> "") / COUNTIF(B2:B9, B2:B9 & "")) when blanks can occur.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

A value that appears n times gets n shares of 1/n, so every distinct value contributes exactly 1 to the total, however often it repeats. SUMPRODUCT is used because it adds an array without special entry in every Excel version.

More to watch out for

Silently wrong: D1003 with a trailing space counts as a fifth debtor, while d1001 merges with D1001. Case is forgiven, spaces are not.
COUNTIF over its own range is n × n comparisons. On a ledger of 50,00050.000 lines that is 2.5 billion tests, and the sheet will hang.

Variations

=ROWS(UNIQUE(B2:B9))

=ROWS(UNIQUE(B2:B9))

Excel 365: UNIQUE removes the repeats (ignoring case as well) and ROWS counts what is left. Fast and readable.

=IF(COUNTIF($B$2:$B$9, B2) > 1, "Several invoices", "")

=IF(COUNTIF($B$2:$B$9; B2) > 1; "Several invoices"; "")

Placed beside each row and copied down, the same COUNTIF flags debtors with more than one open invoice.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Duplicate check on 18-digit bank references

A bank statement export holds 18-digit transaction references, stored as text. You need a column that marks references booked twice, as a lead for duplicate payments. The obvious COUNTIF flags every row, because COUNTIF converts digit-only text to a number and Excel keeps only 15 significant digits. The fix must force a text comparison.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Bank reference	Amount	Check
2	370123450000081501	€4,200.00€ 4.200,00	Unique=LET( ids, A2:A7, hits, COUNTIF(ids, ids & ""), IF(hits > 1, "Duplicate", "Unique") )=LET( ids; A2:A7; hits; COUNTIF(ids; ids & ""); IF(hits > 1; "Duplicate"; "Unique") )
3	370123450000081502	€1,250.00€ 1.250,00	Duplicate
4	370123450000081517	€3,780.50€ 3.780,50	Unique
5	370123450000081502	€1,250.00€ 1.250,00	Duplicate
6	370123450000094220	€960.00€ 960,00	Unique
7	370123450000094231	€2,115.25€ 2.115,25	Unique
8

Formula cell (C2)InputSpilled result

Formula in C2

=LET(
  ids, A2:A7,
  hits, COUNTIF(ids, ids & "*"),
  IF(hits > 1, "Duplicate", "Unique")
)

=LET(
  ids; A2:A7;
  hits; COUNTIF(ids; ids & "*");
  IF(hits > 1; "Duplicate"; "Unique")
)

Result: Unique / Duplicate / Unique / Duplicate / Unique / UniqueUnique / Duplicate / Unique / Duplicate / Unique / Unique

Step by step

LET names the references: ids is A2:A7. ids & "*" builds six patterns, such as 370123450000081501*. The asterisk makes each criterion a wildcard pattern, and wildcard patterns are always compared as text.
COUNTIF runs once per pattern. For the first reference, only A2 matches all 18 characters, so the count is 1. For the second, A3 and A5 match, so the count is 2.
hits becomes {1; 2; 1; 2; 1; 1}.
IF turns every count above 1 into Duplicate and the rest into Unique. The six results spill into C2:C7: rows 3 and 5 are marked Duplicate, both with the amount 1,250.001.250,00, which is a lead worth following up.
Compare the obvious COUNTIF(ids, ids). Each digit-only criterion is read as a number and rounded to 15 significant digits. The first four references all begin with 370123450000081 and become equal, so the counts are {4; 4; 4; 4; 2; 2} and every row is marked Duplicate.

Watch out: The mirror-image mistake: COUNTIF(ids, ids) without the asterisk. It looks like the textbook duplicate check and works on short references, but here it marks all six rows Duplicate.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

COUNTIF decides between a numeric and a text comparison by looking at the criterion. Adding * makes the criterion non-numeric, so all 18 characters are compared, and because all references have the same length the pattern cannot match a longer reference. Passing the whole range as criteria makes COUNTIF return one count per row, and LET keeps the rule readable in a single spilling formula.

Do not use this construction on tens of thousands of rows: it makes n × n comparisons and becomes slow. The alternative is to compare with = instead, SUMPRODUCT(--(A$2:A$7 = A2)) per row, which never converts text to numbers and has no prefix risk, at the cost of one formula per row and the same n × n effort.

More to watch out for

The asterisk means 'starts with'. If references can have different lengths, a short reference also matches every longer one that begins with it. Add a LEN check, or use the = comparison.
A reference that was stored as a number instead of text has already lost its last three digits in the cell itself. No formula can bring them back; format the column as text before importing the file.

Variations

=IF(COUNTIF($A$2:$A$7, A2 & "*") > 1, "Duplicate", "Unique")

=IF(COUNTIF($A$2:$A$7; A2 & "*") > 1; "Duplicate"; "Unique")

One formula per row, copied down, for versions without dynamic arrays.

Try it yourself

Type a criterion and see which journal lines COUNTIF counts.

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Entry no.	Journal
2	25-0401	VRK	Entries	33=COUNTIF(B2:B9, "x")=COUNTIF(B2:B9; "x")
3	25-0402	MEM
4	25-0403	INK
5	25-0404	MEM
6	25-0405	BNK
7	25-0406	VRK
8	25-0407	MEM
9	25-0408

Formula cell
Meets the criterion

=COUNTIF(B2:B9, "MEM")

=COUNTIF(B2:B9; "MEM")

Result: 33

Common mistakes

Common mistakes
Symptom	Cause	Fix
Formula error, or 0 for a limit	The operator is written outside the quotes (`>=E1`) or the cell inside them (`">=E1"`). The first is a formula error; the second compares with the text `E1` and returns 0.	Put the operator in quotes and join the cell with `&`: `">="&E1`.
0 although the value is in the list	A count of 0 for a value you can see in the list, because one side is a number and the other text that looks like one, or because of a trailing space.	Check the cells with ISNUMBER and LEN, then clean the data with VALUE or TRIM.
#VALUE! on a long criterion	A criterion longer than 255 characters, such as a long description, returns #VALUE!.	Compare directly with SUMPRODUCT(--(A2:A50 = E1)), which has no length limit.
0 for a between test in one COUNTIF	Two conditions in one COUNTIF, such as `">=10 and <20"`, return 0.	COUNTIF takes one condition. Use COUNTIFS with the same range twice for a between test.

Used in combinations

Invoices missing from the ledger with FILTER, ISNA and XMATCHList the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.Excel 2021 or later (This combination needs Excel 2021 or later)
Duplicate invoice numbers across two exportsStack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.Excel 2024 or later (This combination needs Excel 2024 or later)
Entries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIFList the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.Excel 2021 or later (This combination needs Excel 2021 or later)
Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later (This combination needs Excel 2021 or later)