Functions and formulas help us perform all sorts of tasks, actions, and calculations in spreadsheets. If you have a Google Sheet where you want to count a number of items that meet a specific condition, you need the COUNTIF function.

Using COUNTIF in Google Sheets, you can save time and manual work from counting the items “by hand.” Simply enter the data range and criterion in the formula to get your result in seconds.

Table of Contents
    How to Use COUNTIF in Google Sheets image

    About the Google Sheets COUNTIF Function

    COUNTIF is a variation of the COUNT function that lets you count cells in a dataset that meet a particular condition. As examples, you may want to count the number of students with a certain GPA, employees who’ve worked a specific number of years with your company, or even car models that begin with a particular letter. With COUNTIF, you have many possibilities to count the data you need quickly.

    The syntax for the function’s formula is COUNTIF(range, condition) with these options for the condition argument:

    • Use a question mark (?) as a wildcard to match a single character.
    • Use an asterisk (*) to match zero or more adjacent characters.
    • To match a question mark or asterisk, place a tilde (~) before it, for example, ~? or ~*.
    • Use the equal sign (=), greater than (>), and less than (<) symbols to compare number equality.
    • Enclose a character string in quotation marks.

    How to Use COUNTIF in Google Sheets

    The best way to explain how to use a function and its formula is to see them in action. Let’s look at a handful of formula examples for COUNTIF in Google Sheets.

    Count Items Greater Than or Equal to a Value

    Using a student grade example, we’ll count the number of students who have a GPA greater than or equal to 3.5.

    Select the cell where you want the result, this is where you’ll type the formula. Enter the following formula making sure to begin with the equal sign and include a comma between the arguments:

    =COUNTIF(B2:B7,”>=3.5”)

    How to Use COUNTIF in Google Sheets image 2
    Online Tech Tips is reader-supported. We may earn a commission when you buy through links on our site. Learn more.

    To break down the formula, B2:B7 is the range of cells and “>=3.5” is the condition for greater than or equal to 3.5.

    As you can see, we receive a result of 3 which is correct. There are three students that have a GPA of 3.5 or higher.

    How to Use COUNTIF in Google Sheets image 3

    Related: Having trouble with formulas? Check out our guide to troubleshooting Google Sheets formula parse errors.

    Count Items Less Than a Value

    In this next example, let’s count the number of employees who have worked for us less than 10 years.

    Select the cell where you want the results and enter the following formula:

    =COUNTIF(B2:B10,”<10″)

    How to Use COUNTIF in Google Sheets image 4

    To break down the formula, B2:B10 is the data range and “<10” is the condition for less than 10.

    We receive 5 as our result which is correct. Notice that Steve Stone has worked for us for 10 years, but he is not part of the result because 10 is not less than 10.

    How to Use COUNTIF in Google Sheets image 5

    Count Items That Start With a Letter

    For another example, let’s count the number of makes of cars, not models, that start with the letter H.

    Select the cell where you want the formula result and type the following:

    =COUNTIF(A2:A9,”H*”)

    How to Use COUNTIF in Google Sheets image 6

    To break down this formula, A2:A9 is our range and “H*” is the condition for the first letter H and the asterisk wildcard for any following letters.

    Here, we receive a result of 4 which is correct; we have four car makes that start with the letter H.

    How to Use COUNTIF in Google Sheets image 7

    Count Items Matching Cell Values

    Maybe the condition you want to match already exists in another cell. You can use this by placing the string in quotation marks, adding an ampersand (&), and entering the cell reference.

    Here, we’ll count the number of times that the value in cell A15 (600) appears in our dataset.

    Select the cell where you want the result and enter this formula:

    =COUNTIF(A2:D13,”=”&A15)

    How to Use COUNTIF in Google Sheets image 8

    Breaking down the formula, A2:D13 is the data range, “=” is the operator (string) in quotes, and &A15 is the value we want to match in cell A15.

    We receive 3 as our result which is correct, we have three items matching 600.

    How to Use COUNTIF in Google Sheets image 9

    Count Items Matching Text Values

    For one final example, you might want to count the number of cells with specific text. Let’s count the total number of expenses for Fuel.

    Select the cell where you want the result and enter this formula:

    =COUNTIF(A2:A8,”Fuel”)

    How to Use COUNTIF in Google Sheets image 10

    To break down this formula, A2:A8 is the data range and “Fuel” is the condition to match.

    We receive our result of 3 which is correct. Note: COUNTIF is not case sensitive, so you could enter “FUEL” or “fuel” for the text string and receive the same result.

    How to Use COUNTIF in Google Sheets image 11

    When you need to count a number of items but only those that meet your criterion, the COUNTIF function in Google Sheets works like a dream.

    If you want to count items that match multiple criteria, you can use the COUNTIFS function. Check out our tutorial for using COUNTIFS along with similar functions in Microsoft Excel and apply the same formula structure in Google Sheets.

    Leave a Reply

    Your email address will not be published. Required fields are marked *