The COUTIF Google Sheets function 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.
This Article Covers:
SYNTAX of the COUNTIF Function in Google Sheets
=COUNTIF(range, criterion)
- range – the range of cells from which you want to get the count.
- criterion – the condition that should be met for a cell to be counted.
The criterion could be:
- a number.
- a text string, such as “Apple” or “Banana”.
- a cell reference (such as A1 or B1).
- an expression such as “=100” or “>100” or “<100”. Note that in the case of equal to, you are not required to use the equal to sign. You can simply use the number in double quotes.
The range of cells could contain numbers or text strings.
- If the range contains numbers, then the criteria could use comparison operators (>, <, =, <>). For example, to count the number of students who scored above 75 in a subject, you can use “>75” as the criterion. The function would check each number in the ‘range’ for this criterion. Note that when you use an operator with a number, you need to enclose it in double quotes.
- If the range contains text strings, the criteria can be the text string within double quotes (e.g., “Apple”) or can be a cell reference that contains the text string.
- You can also use wildcard characters in case of text criterion. For example, to count cells that contain Apple or Apples, you can use Apple* as the criterion.
Now let’s have a look at some example of using COUNTIF Function in Google Sheets.
COUNTIF Google Sheets Function Examples
Here are six examples of using the COUNTIF function in Google Sheets.
Example 1 – Â Counting the Number of Students with More than 75 Marks
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.
=COUNTIF($B$2:$B$14,“>75”)
Note:
- The condition needs to be within double quotes.
- You can also have the criterion value in a cell and use that cell reference. For example, if the criterion is in cell C2, then you can use the formula:Â =COUNTIF($B$2:$B$14,“>”&C2). When you use a cell reference, you still need to have the operator in double quotes and use & to join it with the cell reference.
Example 2 – How to Count Values in Google Sheets for Transaction Accounts
Suppose you have the transaction data as shown below:
Here is the formula that will give us the number of transactions for the US:
Note:
- Since the criterion is text, there is no need to use the equal to sign. For example, in this example, the condition is that the country should be equal to the US, so you can simply use “US”.
- You can also have the criterion in a cell. In that case, you can simply use the cell reference. For example, if the criterion is in cell D2, you can use the formula:Â =COUNTIF($B$2:$B$13,D2)
Example 3 – Counting All the Records Except US with a Google Sheet COUNTIF Function
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:
=COUNTIF($B$2:$B$13,“<>US”)
The above formula would count all the transaction except the ones where the country is ‘US’.
Example 4 – Counting All Empty/Blank Cells in a Range with the COUNTIF Function Google Sheets Syntax
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:
=COUNTIF($C$2:$C$13,“”)
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.
Example 5 – Counting All Non-empty Cells in a Range with the Google Sheets COUNTIF Function
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:
=COUNTIF($C$2:$C$13,“<>”)
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.
Example 6 – Count All Instances where a Keyword Appears
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:
=COUNTIF($A$2:$A$13,“*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.
Using COUNTIFS in Google Sheets
COUNTIFS works similarly to COUNTIF but allows you to select more than one criteria to check against before counting. The syntax for COUNTIFS goes as follows:
= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,…])
As you can see, it’s exactly the same as COUNTIF but with additional critera_range(s) and conditions. You can add as many as you’d like. Let’s take a look at an example to see how it works.
In the below example, we need to see if the people in the data set have brown eyes, brown hair, and tested positive for a genetic condition.
We used a COUNTIFS formula with multiple criteria to check against each requirement to return a result of 1.
COUNTIF Formula Google Sheets Tips and Troubleshooting
- The COUNTIF formula can only use one criteria, use COUNTIFS for multiple criteria
- Text arguments aren’t case sensitive
- Don’t forget to use quotes on text strings and logical operators
How to Use COUNTIF in Google Sheets FAQ
How Do You COUNTIF a Range of Numbers in Google Sheets
- Type =COUNTIF( into an empty cell you wish to show the results in
- Highlight the range you wish to count then type a comma ,
- Type the criteria to be checked
- Press enter
Can You Do a COUNTIF With Multiple Criteria Google Sheets?
No, you have to use the COUNTIFS formula instead. It works the same as COUNTIF but allows for multiple criteria.
What Is the Criteria in COUNTIF?
You can picture the criteria as the IF statement you want to check against. For example, if you wanted to count how many people scored over 80 on a test, your criteria could be “>80”
How Do You Do a COUNTIF With Two Criteria Sheets?
You have to use COUNTIFS instead with the following syntax:
= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,…])
If You Found This COUNTIF Google Sheets Tutorial Useful, You May Also Like the Following Tutorials:
- Using IF Function in Google Sheets.
- Using VLOOKUP Function in Google Sheets.
- Using COUNTIF Function in Excel.
- Creating a Drop Down List in Google Sheets.
- Using IF Function in Google Sheets.
- Using IFS Function in Google Sheets.
- Using IFERROR Function in Google Sheets.
- Using OR Function in Google Sheets.
- How to Use Count Function In Google Sheets for Empty Cells
Sumit
Google Sheets and Microsoft Excel Expert.
2 thoughts on “A COUNTIFS and COUNTIF Google Sheets Guide for 2022”
What if I wanted the “countif” function to count Apples even if it said Green Apples or Red Apples as well as if it said Apple or Apples? I notice that the Apple* function only works if it STARTS with apples, but I want it to count the cell even if apples APPEARS ANYWHERE. How do I do that?
*apple*
Comments are closed.