COUNTIF

Returns a conditional count across a range.

COUNTIF Function

For this same video with audio descriptions: COUNTIF function

To get an example spreadsheet and follow along with the video, click 'Make a copy' below.

Make a copy

COUNTIF for BigQuery

Returns a conditional count of rows across a data column.

Sample usage

COUNTIF(table_name!score, “>90”)

Syntax

COUNTIF(criteria_column, criterion)

• criteria_column: The data column that is tested against the 'criterion'.
• criterion: The pattern or test to apply to the 'column'.

Tip: Counting unique rows across multiple columns is not supported

Sample usage

COUNTIF(A1:A10,">20")

COUNTIF(A1:A10,"Paid")

Syntax

COUNTIF(range, criterion)

• range: The range that is tested against the criterion.

• criterion: The pattern or test to apply to the range.

  • If the range contains text to check against, the criterion must be a string. The criterion can contain wildcards, including ? to match any single character or * to match 0 or more contiguous characters. To match an actual question mark or asterisk, prefix the character with the tilde (~) character (i.e. ~? and ~*). To match an actual tilde, prefix the character with another tilde (i.e. ~~). A string criterion must be enclosed in quotation marks. Each cell in the range is then checked against the criterion for equality (or a match, if wildcards are used).

  • If the range contains numbers to check against, the criterion may be either a string or a number. If a number is provided, each cell in the range is checked for equality with the criterion. Otherwise, the criterion may be a string containing a number (which also checks for equality) or a number prefixed with any of the following operators: =, >, >=, < or <=, which check whether the range cell is equal to, greater than, greater than or equal to, less than, or less than or equal to the criterion value, respectively.

Notes

• COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA.

• COUNTIF is not case-sensitive.

Examples

Make a copy

See also

COUNTIFS: Returns the count of a range depending on multiple criteria.

SUMIF: Returns a conditional sum across a range.

DCOUNTA: Counts values, including text, selected from a database table-like array or range using a SQL-like query.

DCOUNT: Counts numerical values selected from a database table-like array or range using an SQL-like query.

COUNTUNIQUE: Counts the number of unique values in a list of specified values and ranges.

COUNTA: Returns the number of values in a dataset.

COUNTBLANK: Returns the number of empty cells in a given range.

COUNT: Returns the number of numeric values in a dataset.