COUNTIF Function in Google Sheets

COUNTIF in Google Sheets counts how many cells in a range match a condition you specify. It handles text, numbers, dates, and pattern matching with wildcards, making it one of the most used counting functions in Sheets.

You provide a range and a criterion. COUNTIF checks each cell and returns the count of matches.

Parameters

Parameter	Required	Description
range	Yes	The range of cells to count. Can include numbers, text, dates, or blank cells.
criterion	Yes	The condition that determines which cells to count. Supports exact values, comparison operators (">10"), and wildcards (* for any characters, ? for a single character). Text criteria are not case-sensitive.

Examples

Count cells with specific text

Count how many times “Completed” appears in a status column:

=COUNTIF(C2:C100, "Completed")

This returns the number of cells in C2:C100 that contain exactly “Completed”. The match is not case-sensitive, so “completed” and “COMPLETED” both count.

Count values above a threshold

Count how many sales exceed 500:

=COUNTIF(B2:B50, ">500")

The comparison operator and value go inside the same set of quotes. This counts every cell in B2:B50 where the value is strictly greater than 500.

Count with wildcards

Count all product codes that start with “SKU-”:

=COUNTIF(A2:A100, "SKU-*")

The asterisk (*) matches any sequence of characters. This counts “SKU-101”, “SKU-999”, and “SKU-A” alike. Use ? to match exactly one character, so "SKU-???" matches only three-character suffixes like “SKU-101” but not “SKU-99”.

Common Errors

#VALUE! --- The criterion is malformed. This usually happens when comparison operators are split from their values or when the criterion string is not wrapped in quotes. Write ">10" as a single quoted string, not ">" & 10 split across multiple arguments.

Unexpected zero --- COUNTIF returns 0 when no cells match. Check for extra spaces in your data using =TRIM() or verify that you are comparing the right data types. A cell containing the number 100 does not match the text “100”.

Tips

To count cells that match a value stored in another cell, pass the cell reference directly: =COUNTIF(A2:A100, E1). No quotes needed around the cell reference.

If you need to count based on multiple conditions (for example, region is “East” AND status is “Completed”), use COUNTIFS instead. COUNTIF only supports a single condition.