COUNTIF Function – Google Sheets

The COUNTIF function is used in Google Sheets to count cells if data meets a specific condition.

COUNTIF checks cells for a condition and then counts every cell in the range that meets it. Use the COUNTIFS function if you have more than one criteria.

Get your copy of these examples in a live Google Sheet.

Purpose

This function returns the number of cells that meet a specified criterion in a data set.

Syntax

=COUNTIF(range, criterion)

  • range – The value or range to consider while counting.
  • criterion – The condition tested against each cell in the range.

Similar Functions

COUNT – Counts the numeric values in a data set

COUNTA – Counts the non-blank cells in a data set

Video Tutorial

Video thumbnail
YouTube Video About COUNTIF

Examples

Example 1 – Greater Than An Amount

First, we will count all of the cells in A2:A5 that contain a value greater than 37. Counting numbers is a straightforward use of the function.

COUNTIF function for amounts greater than 37
Greater than 37

The function counts three of the four numbers. Notice the quotes around ">=37". Even though 37 is not a Text value, the COUNTIF function requires quotes around the condition. Typically quotes are used in spreadsheet formulas when referring to text. Read more about this inconsistency here.

Example 2 – Before a Certain Date

Next, let’s look at counting dates before a point in time.

COUNTIF function for dates before 2/23/2022
Before February 23, 2022

Here we are using "<2/23/2022" to specify all dates before February 23, 2022, which is 2 of the 4 dates.

What if we had a mix of different data types?

Example 3 – Is A Certain Word

COUNTIF can also match text values.

Counting the cells that contain the string "Sunny"
Contains only the word “Sunny”

Here we count all cells that contain the string "Sunny" and only that string. Notice that we use quotes in this function standardly as they should always surround strings.

Example 4 – Contains A Certain Word (And Maybe Others)

In addition to completely matching a string, you can use wildcards for partial matches.

Counting the cells that have the word "Sunny" anywhere in the string
Has the word “Sunny” with any words before

Wildcards can represent different characters in Google Sheets. An asterisk (*) is any number of characters, a question mark (?) is any single character, and the tiled (~) escapes the asterisk or question mark, which tells Google Sheets to interpret it literally. In the formula above, we are looking for any string of characters that ends with the string Sunny.

Example 5 – Is True

COUNTIF can also deal with boolean values.

Counting the cells that have a value of TRUE
Are TRUE

Boolean values are either TRUE or FALSE. The value can be typed into a cell, the result of a check box, or the output of a function. Since boolean values are not text, you do not surround them with quotes.

Example 6 – Is Blank

COUNTIF function for cells that are blank
Blanks

There may be times when you want to count empty cells. You can do this by using quotes with nothing in between. Be sure not to use a space between the quotes, and be aware that invisible characters (AKA non-printing) are not blanks.

Example 7 – Is Not Equal To

The last example is counting everything except a specified value.

Counting amounts not equal to 42
All Numbers Except 42

In this case, we are counting all values except for those equal to 42.

Live Examples in Sheets

Go to this spreadsheet for examples of the COUNTIF functions shown above that you can study and use anywhere you would like.

Notes

  • Comparison operators such as >,<,= must be enclosed in quotes like “>45“.
  • Don’t enclose cell references in quotes.
    • If you are combining comparison operators and cell references, use an ampersand (&) to join them like A1&">45".
  • Text must be in quotation marks, such as “North”
  • Text strings can contain wildcard characters ? and * . The * matches any characters and ? matches any one character. *apple would match green apple and red apple. Do? would match dog and dot.

Leave a Comment