COUNTIF Function – Google Sheets

The COUNTIF function is used in Google Sheets to count cells if their values meet a specified condition. Think of this function as a combination of COUNT and IF.

💡 Use the COUNTIFS function if you have more than one criterion.

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 – Count the numeric values in a data set

COUNTA – Count the non-blank values in a data set

COUNTIF – Count the cells that match a criterion

COUNTIFS – Count the cells that match multiple criteria

COUNTUNIQUE – Count the unique values

COUNTUNIQUEIFS – Count the unique values that meet multiple criteria

COUNTBLANK – Count the blank cells

Video Tutorial

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.

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 only 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.

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

What about data types other than numbers?

Insert Math Symbols (Add-On)

Example 3 – Is A Certain Word

COUNTIF can also match text values.

Here we count all cells that contain the string "Sunny" and only that string. Notice that we use quotes in this function normally, 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.

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.

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

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.

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 want.

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".
• The 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.