Last Updated on October 25, 2021 by Jake Sheridan
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
THe simplest type of COUNTIF criteria is an exact match. Here’s how to check for exact matches with COUNTIF:
Select the cell you want to perform the count in and type “=COUNTIF(“ to start your COUNTIF formula
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
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
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
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
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
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”
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
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.