COUNTIF Function in Google Sheets can be used when you want to get the count when a given condition is met.
For example, if you have a list of students and you want to quickly get the count of all the students who have scored above 75, then you can use the COUNTIF Function in Google Sheets.
Before I show you some cool examples of using the COUNTIF Function, let’s quickly go through the syntax and some important things about it.
The criterion could be:
The range of cells could contain numbers or text strings.
Now let’s have a look at some example of using COUNTIF Function in Google Sheets.
Here are six examples of using the COUNTIF function in Google Sheets.
Suppose you have a list of students with the marks scored in a test.
Here is the formula that will give you the count of students who scores above 75.
Suppose you have the transaction data as shown below:
Here is the formula that will give us the number of transactions for the US:
Suppose you have the same transaction data (used in example 3) and you want to count all the transaction records except for those of the US.
Here is the formula that will do this:
The above formula would count all the transaction except the ones where the country is ‘US’.
If you have a huge dataset and you want to get a count of missing records or empty or blank cells, you can use the COUNTIF function to do this.
Suppose you have a dataset as shown below:
Here is the formula that will give you the total number of empty or blank cells in it:
In this example, the criteria used is “” (double quotes). When only double quotes are used as criteria, it counts only those cells that are empty/blank. Note that if there is a cell that looks blank but has a space character in it, then it will not be counted by this formula.
Taking the same data from example 4, in this example let’s see how to count all the non-empty cells in the dataset.
Here is the formula that will count all the non-empty cells:
In this example, the criteria used is “<>” (not equal to sign within double quotes). This criterion means that the cells are not equal to an empty string. Note that if there is a cell that looks blank but has a space character in it, then it will be counted by this formula.
Sometimes, you may have to deal with data that contains variations of the same name or item. For example, in the data below, there are variations of Apple as Apple, Apples, Fruit – Apple.
Here is the formula you can use to count the total number of cells with the keyword Apple:
In the above example, the criterion used is *Apple*. An asterisk (*) is a wildcard character that represents any number of characters. So *Apple* would count any cell that has the text Apple in it and it could be flanked by any number of characters on either side.
You May Also Like the Following Tutorials: