How to Use COUNTIF in Google Sheets: Comprehensive Guide

COUNTIF counts the cells in a range that meet a condition. It answers "how many orders are still open?", "how many scores are above 80?" and "how many times does this name appear?", and it is also the quickest way to spot duplicates.

Syntax

=COUNTIF(range, criterion)

range: The cells to examine.
criterion: The condition a cell must meet to be counted.

Basic examples

=COUNTIF(A2:A100, "Open")

Counts the cells equal to Open (not case-sensitive).

=COUNTIF(B2:B100, ">80")

Counts the numbers greater than 80.

=COUNTIF(A2:A100, E1)

Counts the cells equal to whatever is in E1.

Writing criteria

Exact text or number: "Open", 5, or "=5"
Comparison: ">100", ">=18", "<>0" (not equal to 0), "<>Open"
Wildcards: "App*" (starts with), "*corp" (ends with), "*sheet*" (contains), "?????" (exactly five characters). To count a literal * or ?, put ~ in front.
Blank cells: "=" or ""
Non-blank cells: "<>"
Operator plus a cell: ">" & E1

=COUNTIF(B2:B100, ">" & AVERAGE(B2:B100))

Counts the values above the average.

Counting dates

=COUNTIF(A2:A100, ">=" & DATE(2026, 1, 1))

Counts dates on or after January 1, 2026. Count today's entries with =COUNTIF(A2:A100, TODAY()). For a date range with two limits, use COUNTIFS.

Practical patterns

Find duplicates

Highlight or flag repeated values with a helper column:

=IF(COUNTIF($A$2:$A$100, A2) > 1, "Duplicate", "")

To use it in conditional formatting, apply the custom formula =COUNTIF($A$2:$A$100, $A2) > 1.

Check whether a value exists in a list

=COUNTIF(Lists!A:A, E2) > 0

Returns TRUE when the value is in the list, which works well for validation rules and IF tests.

Count the unique values in a range

=SUMPRODUCT(1 / COUNTIF(A2:A100, A2:A100))

This gives each item a weight of one divided by its number of occurrences, which adds up to the number of distinct items. It requires no blank cells in the range. A simpler modern alternative is =COUNTA(UNIQUE(A2:A100)).

Percentage that meets a condition

=COUNTIF(B2:B100, ">=60") / COUNT(B2:B100)

For example, the pass rate for a set of scores.

Rank without a rank function

=COUNTIF($B$2:$B$100, ">" & B2) + 1

The number of larger values, plus one, gives the rank.

Count with OR logic

COUNTIF takes one condition. Add counts, or pass an array of values:

=SUM(COUNTIF(A2:A100, {"Open", "Pending"}))

Video Example

Common problems

Count is lower than expected. The usual causes are trailing spaces ("Open " is not "Open") and numbers stored as text. Use TRIM to clean the data. Capitalization does not matter.
Wildcards counting more than intended. If your data contains * or ? characters, escape them with ~.
Operator in the wrong place. COUNTIF(A1:A10, >5) is an error; write ">5".
Counting across several columns. COUNTIF(A2:C100, "Open") works on a two-dimensional range and counts every matching cell.
More than one condition. Use COUNTIFS.

COUNTIF vs. COUNT vs. COUNTA vs. COUNTIFS

COUNT counts numeric cells.
COUNTA counts non-empty cells.
COUNTIF counts cells that meet one condition.
COUNTIFS counts cells that meet several conditions at once.

Frequently asked questions

Is COUNTIF case-sensitive? No. Use SUMPRODUCT(EXACT(...)) for case-sensitive counting.

Can COUNTIF count across sheets? Yes: =COUNTIF(Orders!A:A, "Open").

Can I count cells by background color? Not with a built-in function. Use a helper column or Apps Script.

Why does COUNTIF return 0? Check that the criterion is quoted properly and that the data doesn't have hidden spaces or text-number mismatches.

Related Functions

COUNTIFS: Count with several conditions.
COUNT: Count numeric cells.
COUNTA: Count non-empty cells.
SUMIF: Sum cells that meet a condition.
UNIQUE: Return distinct values.

COUNTIF