COUNTIF and COUNTIFS functions in Google Sheets

COUNTIF counts the cells that meet one condition; COUNTIFS counts the rows that meet several.

COUNTIF answers "how many?" with a condition: how many passed, how many orders are pending, how many times a name appears. COUNTIFS adds more conditions.

Syntax

=COUNTIF(range, criterion)

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)

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

Examples

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
Students in class 8A.
=COUNTIF(C2:C9,">=75")
Result: 4
Maths marks of 75 or more.
=COUNTIF(A2:A9,"*a")
Result: 4
Names ending in a.
=COUNTIF(C2:C9,">"&AVERAGE(C2:C9))
Result: 5
Marks above the average.
=COUNTIFS(B2:B9,"8A",C2:C9,">=60")
Result: 3
Two conditions: class 8A with at least 60 in Maths.
=COUNTIFS(C2:C9,">=40",C2:C9,"<60")
Result: 2
A band: from 40 up to 59.
=ARRAYFORMULA(COUNTIF(B2:B9,{"8A";"8B"}))
Result:
4
4
In ARRAYFORMULA, COUNTIF counts for each item of a list.

How they work

COUNTIF checks each cell of a range against a condition and counts those that pass. COUNTIFS takes pairs of range and condition and counts the rows where all of them hold.

Conditions are written as for SUMIF: text and comparisons in quotation marks, cell references joined on with &, and the wildcards * and ?.

Useful special cases:

"" counts empty cells, and "<>" counts cells that are not empty.
"?*" counts cells containing at least one character of text.

Duplicates

=COUNTIF(A:A, A2)>1 is TRUE for any value that occurs more than once. As a conditional formatting rule (Format, Conditional formatting, Custom formula is), it colours the duplicates.
=COUNTIF(A$2:A2, A2) numbers each occurrence, 1 for the first, 2 for the second.

Sheets also offers Data, Data clean-up, Remove duplicates for deleting them.

Is a value in another list?

=COUNTIF(Other!A:A, A2)>0 tells whether A2 appears in the other tab. It is the simplest way to compare two lists.

A count for every item, from one formula

Inside ARRAYFORMULA, COUNTIF accepts a list of criteria, as the last example shows. Combined with UNIQUE it makes a frequency table in two cells:

H2: =UNIQUE(B2:B)
I2: =ARRAYFORMULA(IF(H2:H="", "", COUNTIF(B2:B, H2:H)))

One formula does it all with QUERY: =QUERY(A1:E, "select B, count(A) where B is not null group by B", 1).

COUNTIFS, like SUMIFS, does not expand over a list of criteria.

Counting different values

To count how many different values a range holds, use COUNTUNIQUE, described on the UNIQUE page. COUNTUNIQUEIFS adds conditions.

Counting by a pattern or a calculation

COUNTIF needs a real range and a simple condition. For a test on a calculation, such as the month of a date or a text pattern, count the rows that FILTER returns, or add up a test:

=COUNTA(FILTER(A2:A13, MONTH(A2:A13)=3))
=SUMPRODUCT(REGEXMATCH(B2:B13, "(?i)^a"))

Tick boxes

A column of tick boxes holds TRUE and FALSE. =COUNTIF(D2:D50, TRUE) counts the ticked ones, which makes a quick progress counter for a task list.

Common mistakes

Comparison signs outside the quotation marks.
COUNTIFS ranges of different sizes.
A band that loses its edge, such as ">40" with "<60" when 40 should count.
Stray spaces, so that "8A " is not counted as "8A".
Expecting an empty cell to pass ">=0". It does not.

In Excel

COUNTIF and COUNTIFS are the same in Excel. COUNTUNIQUE is not available there.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

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