How To Use Countif In Google Sheets

In this tutorial, you will learn how to use countif in Google Sheets.

The COUNTIF function in Google Sheets is extremely useful any time you want to determine the number of times a certain condition has been met. For example, say you want to know how many of your customers come from a given city, or how many times a given item has sold. Instead of scanning through your spreadsheet and counting each of these items, you can use the COUNTIF() function to automatically do the count for you.

In addition to simply checking if cells are equal to a given value, COUNTIF can be used to check if they meet more complex criteria. For example, you can check if cell values are greater than, less than, or not equal to a given number, or you can count all instances where a keyword or string appears within text

Exact Match

THe simplest type of COUNTIF criteria is an exact match. Here’s how to check for exact matches with COUNTIF:

Step 1

Select the cell you want to perform the count in and type “=COUNTIF(“ to start your COUNTIF formula

Step 2

Now, type the reference to the range you want the formula to look at. For each cell in this range that meets the criteria, the count will increase by 1

Step 3

Type in the value to look for. In this case we’ll use the text “New York” to count how many customers come from New York. Be sure that any text is enclosed in quotation marks. Numbers do not need to be

Step 4

Lastly, type a closing parenthesis and hit enter to finish your formula. The total number of times that value appears in the specified range will be displayed in the cell

Greater Than & Less Than

Step 1

To check if values are greater than a given number, use this formula: “=COUNTIF(C2:C,”>21″)”. The criteria here is “>21” and it checks if cell values are greater than 21. Use the less than symbol (<) instead to check for values less than a given number

Step 2

You can also count instances that match the given number by adding an equals sign: “=COUNTIF(C2:C,”>=21″)” or check if

Count Cells Not Equal To a Value

Step 1

To count cells that are not equal to a given value, use this formula: “=COUNTIF(C2:C,”<>Riverside”)”. The criteria here is “<>Riverside”. The <> means “not equal to” for formulas, so this could be read as “Not equal to Riverside”

Using Wildcards

Step 1

You can also search for cells that contain certain text, even if they also contain other text as well. Here’s the formula: “=COUNTIF(A2:A,”*Jane*”)”. This formula will count all cells that contain “Jane” even if they also contain other characters

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to use countif in Google Sheets. Want more? Check out all the Google Sheets Tutorials.