COUNTIF function in Excel

COUNTIF counts the cells in a range that meet one condition.

COUNTIF answers "how many?" with a condition attached: how many students passed, how many orders are pending, how many times a name appears. Along with SUMIF, it is the function most used to summarise a list.

Syntax

=COUNTIF(range, criteria)

Argument	What it is
`range`	The cells to look at.
`criteria`	The condition: a value, a comparison in quotation marks, or a cell reference.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=COUNTIF(B2:B9,"8A")
Result: 4
How many students are in class 8A.
=COUNTIF(C2:C9,">=75")
Result: 4
How many Maths marks are 75 or more.
=COUNTIF(C2:C9,"<40")
Result: 1
How many are below 40.
=COUNTIF(C2:C9,78)
Result: 2
How many times the exact mark 78 appears.
=COUNTIF(A2:A9,"*a")
Result: 4
A wildcard: names that end with the letter a.
=COUNTIF(C2:C9,">"&AVERAGE(C2:C9))
Result: 5
A comparison with a calculated value: how many marks are above the average.
=COUNTIF(C2:E2,">=80")
Result: 2
Along a row: in how many subjects Asha scored 80 or more.
=COUNTIF(C2:C9,C2)>1
Result: TRUE
A duplicate test: is the value in C2 found more than once in the column?

How it works

COUNTIF looks at each cell of the range, tests it against the condition and counts the cells that pass. It has only two arguments, so there is nothing to line up.

Writing the condition

To count	Write
cells equal to a text	`"8A"`
cells equal to a number	`78`
cells above a number	`">75"`
cells at or below a number	`"<=40"`
cells not equal to something	`"<>Pen"`
cells equal to the value in H1	`H1`
cells above the value in H1	`">"&H1`
text that starts with A	`"A*"`
text that contains "kg"	`"kg"`
text of exactly four characters	`"????"`
empty cells	`""`
cells that are not empty	`"<>"`

Two rules cover most mistakes. A comparison sign goes inside quotation marks. A cell reference stays outside them and is joined on with &.

Text matching ignores capital and small letters.

Finding duplicates

COUNTIF is the standard tool for spotting repeated values.

In a helper column, =COUNTIF($A$2:$A$100,A2) shows how many times each entry occurs. Anything above 1 is repeated.
=COUNTIF($A$2:A2,A2) with a range that grows as it is copied down numbers each occurrence: 1 for the first, 2 for the second. Filtering for values above 1 shows only the extra copies.
As a Conditional Formatting rule, =COUNTIF($A$2:$A$100,A2)>1 colours every duplicate.

Is this value in that list?

=COUNTIF(list, A2)>0 is TRUE when A2 appears in the list. Put it beside one list to check each entry against another. It is the simplest way to compare two lists.

A percentage

Divide by the total count for a share: =COUNTIF(C2:C9,">=33")/COUNT(C2:C9) is the pass rate. Format the cell as a percentage.

Counting within bands

For "from 40 up to 59" you need two conditions, which is COUNTIFS. With COUNTIF alone, subtract: =COUNTIF(C2:C9,">=40")-COUNTIF(C2:C9,">=60").

Limits and surprises

Long numbers. COUNTIF compares only the first 15 digits of a number, so two different 16-digit card or account numbers stored as text can count as equal.
One condition. For more, use COUNTIFS.
Calculations on the range are not allowed. =COUNTIF(MONTH(A2:A13),3) is not accepted, because the first argument must be a real range. Use SUMPRODUCT for that.
Closed workbooks. A COUNTIF that points to another workbook shows #VALUE! when that workbook is closed.

In Google Sheets

COUNTIF is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.