How to Use the Google Sheets COUNTIF Function

This article explains how to use the COUNTIF function in the web and mobile versions of Google Sheets.

COUNTIF Syntax and Arguments

The COUNTIF function combines the IF function and COUNT function in Google Sheets. This combination counts the number of times specific data is found in a selected range of cells that meets a single, specified criterion. The IF portion of the function determines which data meets the criterion. The COUNT portion totals the number of cells that meet the criterion.

A function's syntax refers to the function's layout and includes the function name, brackets, comma separators, and arguments. The syntax for the COUNTIF function is:

=COUNTIF(range, criterion)

The range is the group of cells the function will search.

If the range argument contains numbers:

• A comparison operator such as  > (greater than), <= (less than or equal to), or <> (not equal to) can be used in an expression. Each cell in the range is checked to determine if it meets the criterion.
• For criterion that searches for equal values, the equal sign (=) does not need to be included in the expression, and the value does not need to be enclosed in quotation marks. For example, 100 can be used for the criterion argument instead of "=100," although both will work.
• For non-equal expressions that don't include cell references, enclose the expression in double quotation marks (for example, "<=1000").
• For expressions that use comparison operators and cell references, cell references are not enclosed in double quotation marks, such as "<>"&B12 or "<="&C12.
• For expressions that use comparison operators and cell references, the comparison operator is joined to the cell reference with an ampersand (&), which is the concatenation character in Excel and Google Sheets, for example, "<>"&B12 or "<="&C12.

If the range argument contains text data:

• Text strings are enclosed in double quotation marks (for example, "drapes").
• Text strings can contain the ? and * wildcard characters to match one (?) or multiple (*) contiguous characters.
• To match an actual ? or *, enter a tilde before these characters, for example, ~? and ~*.

The criterion determines whether a cell identified in the range argument is counted or not. The criterion can be:

• A number.
• A cell reference to the location of data in a worksheet, such as B12.
• An expression, such as 100, "<=1000" or "<>"&B12.
• Text data or a text string, "Drapes" is an example.

COUNTIF Function Examples

The COUNTIF function demonstrated in this article finds the number of cells of data in column A that match various criteria. The COUNTIF formula results are displayed in column B and the formula is shown in column C.

• The first five rows of the example have text data for the function's criterion argument and use cells A2 to A6 for the range argument.
• The last five rows have number data for the criterion argument.

How to Enter the COUNT Function

Google Sheets does not use dialog boxes to enter function arguments as found in Excel. Instead, it has an auto-suggest box that appears as the name of the function is typed into a cell. 

The steps below show how to enter the COUNTIF function and its arguments located in cell B11. In this cell, COUNTIF searches the range A7 to A11 for numbers that are less than or equal to 100,000.

To enter the COUNTIF function and its arguments as shown in cell B11 of the image:

1. Select cell B11 to make it the active cell. This is where the results of the COUNTIF function will display.

2. Type the equal sign (=) followed by the name of the function countif.

3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter C.

4. When the name COUNTIF appears in the box, press Enter to enter the function name followed by a round bracket.

5. Highlight cells A7 to A10 to include these cells as the range argument.

6. Type a comma to act as a separator between the range and criterion arguments.

7. After the comma, type the expression "<="&C12 to enter it as the criterion argument.

8. Press Enter to complete the function.

9. The answer 4 appears in cell B11, since all four cells in the range argument contain numbers less than or equal to 100,000.

Select cell B11 to see the completed formula in the formula bar above the worksheet:

=countif (A7:A10, "<="&C12