Google Sheets COUNTIF Function

COUNTIF returns a conditional count across a specific range

Format
mla apa chicago
Your Citation
French, Ted. "Google Sheets COUNTIF Function." ThoughtCo, Jul. 10, 2017, thoughtco.com/countif-count-data-matching-set-criterion-3123945. French, Ted. (2017, July 10). Google Sheets COUNTIF Function. Retrieved from https://www.thoughtco.com/countif-count-data-matching-set-criterion-3123945 French, Ted. "Google Sheets COUNTIF Function." ThoughtCo. https://www.thoughtco.com/countif-count-data-matching-set-criterion-3123945 (accessed September 23, 2017).
Finding Data that Matches Set Criterion with Google Spreadsheets' COUNTIF Function
Finding Data that Matches Set Criterion with the COUNTIF Function. © Ted French

The COUNTIF function combines the IF function and COUNT function in Google Sheets. This combination allows you to count the number of times specific data is found in a selected range of cells that meets a single, specified criterion. Here's how the function works:

  • The IF portion of the function determines which data meets the specified criterion.
  • The COUNT portion of the function totals the number of cells that meet the criterion.

    The COUNTIF Function's Syntax and Arguments

    A function's syntax refers to the layout of the function and includes the function's 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 is to search. 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 text string, such as "Drapes"

    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 and each cell in the range is checked to see if it meets the criterion.
    • For criterion searching 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 do not include cell references, the entire expression must be enclosed in double quotation marks, such as "<=1000"
    • For expressions using comparison operators and cell references, the cell references are not enclosed in the double quotation marks, such as "<>"&B12 or "<="&C12.
    • For expressions using comparison operators and cell references, the comparison operator is joined to the cell reference using the ampersand (&), which is the concatenation character in Excel and Google Sheets, such as "<>"&B12 or "<="&C12.

    If the range argument contains text data:

    • Text strings must be enclosed in double quotation marks, such as "drapes."
    • Text strings can contain the ? and * wildcard characters to match one (?) or multiple (*) contiguous characters.
    • To match an actual ? or *, precede these characters with a tilde such as ~? and ~*.

    COUNTIF Function Examples

    As shown in the image accompanying this article, the COUNTIF function is used to find 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 itself 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.

    Entering the COUNT Function

    Google Sheets does not use dialog boxes to enter a function's arguments like you find in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

     The steps below detail entering the COUNTIF function and its arguments located in cell B11 of the example image. 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. Click on cell B11 to make it the active cell. This is where the results of the COUNTIF function will be displayed.
    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 the Enter key on the keyboard to enter the function name and open round bracket into cell B11.
    5. Highlight cells A7 to A11 to include them as the function's range argument.
    1. Type a comma to act as a separator between the range and criterion arguments.
    2. After the comma, type the expression "<="&C12 to enter it as the criterion argument.
    3. Press the Enter key on the keyboard to enter a closing round bracket and complete the function.
    4. The answer 4 should appear in cell B11 since all four of the cells in the range argument contain numbers less than or equal to 100,000.
    5. When you click on cell B11, the completed formula =countif (A7:A10, "<="&C12 appears in the formula bar above the worksheet.
    Format
    mla apa chicago
    Your Citation
    French, Ted. "Google Sheets COUNTIF Function." ThoughtCo, Jul. 10, 2017, thoughtco.com/countif-count-data-matching-set-criterion-3123945. French, Ted. (2017, July 10). Google Sheets COUNTIF Function. Retrieved from https://www.thoughtco.com/countif-count-data-matching-set-criterion-3123945 French, Ted. "Google Sheets COUNTIF Function." ThoughtCo. https://www.thoughtco.com/countif-count-data-matching-set-criterion-3123945 (accessed September 23, 2017).