The COUNTIF function is an excellent tool in Google Sheets to make your data analysis more manageable and smoother. It is a perfect way to tally specific information or keep track of the occurrences in your spreadsheet. Go to the “Insert” tab of the main menu and choose the “Function” from the list; then, you will find the COUNTIF function in the “Math” category.
Did you ever find it tricky to count cells that contain specific values in Google Sheets?
If so, you are not alone. It happens to each one of us.
Whether you are a beginner or an experienced spreadsheet user, the COUNTIF function is a versatile tool to make your data assessment simple – no more confusion, just easy counting and analysis.
It has been proven to be a stress-free way to keep track of things in your sheets.
Let’s dive into the world of spreadsheet magic by using the super tool – COUNTIF, where we’ll be learning how to use the COUNTIF function in Google Sheets with examples for your clear understanding.
Download The Example Google Sheet
Before we jump into the discussion, make sure to download the following Google Sheet, which contains the datasets used to demonstrate various examples of the COUNTIF function.
If you have your dataset ready to practice the examples we are about to discuss in the following section of this article, then skip downloading the above file.
Note that practicing the COUNTIF function examples discussed below will strengthen your knowledge and skills.
Why should you use the COUNTIF function?
As mentioned earlier, the COUNTIF function in Google Sheets helps you count the number of cells in a specified range.
Here are some scenarios where you can use the COUNTIF function:
- Counting items based on single criteria – Use the COUNTIF function to count the number of cells based on a condition. For example, consider you are counting the number of students who have scored above 85 in Mathematics. In other words, the function helps users with frequency analysis. They can determine the number of times an event has occurred under a specific condition.
- Tracking occurrences that satisfy multiple conditions – It can be used with comparison operators to count cells that meet a particular condition. For example, consider that you wish to find the number of students who have completed the test and the number of students who have scored more than 90 on the examination.
- Counting Cells with Text or Values – The COUNTIF function can be used to count the occurrence of a specific text or value. You can also identify blank or non-blank and duplicates.
The applications of the COUNTIF function vary depending on the scenario. You can easily create complex formulas using arithmetic operators.
Explained: The COUNTIF Function in Google Sheets
The COUNTIF is a powerful function that offers a quick and effortless way to count the number of cells that satisfy a specific criterion.
Anyone can use this function, from beginners to advanced users. The general syntax for the formula is as follows,
=COUNTIF(range, criterion)Each argument of the above formula needs to be replaced with the proper information.
- “range” – It represents the range where you wish to look for a specific text or value
- “criterion” – Here, you can define the criteria using the arithmetic operators or Wildcraft characters to count the cells that satisfy the condition
Make sure to use the double quotation marks to enclose the defined criteria.
How to Use the COUNTIF Function in Google Sheets
The COUNTIF function has a simple syntax. It enables users to count the text, numbers, and blank cells quickly.
It can be used for various scenarios as follows,
- Counting the cells containing entire text string
- Counting the cells containing the portion of a given text string
- Counting the cells containing exact number
- Counting the cells greater or less than a given number
- Counting blank and non-blank cells
Let’s discuss each with an example in the following sections.
EXAMPLE #1 – Using the COUNTIF function to find the cells that contain a specific Text String (Exact Match)
This is the most common scenario where we will be counting the cells that contain a specific text.
Consider the following example. Here, we have employee data with names, departments, and annual salaries.
Our task is to count the number of employees working in the IT department.
Let’s start,
- Click on the desired cell
- Type “=countif”
- Select the first option from the popup or press the “Tab” on your keyboard
- Replace the range argument with the cell range “B2:B11“
- Put “,” to move to the next argument
- Next, type “IT” in the place of the criterion argument
(Make sure to use the double quotation marks; otherwise, the formula will return an error at the end) - Complete the bracket using the “)“
- Press the “Enter” key
Here’s how our final looks,
=COUNTIF(B2:B11,"IT")Google Sheets will instantly count the cells containing the text “IT” in column B. As seen in the above GIF, the number of employees working in the IT department is 4.
You can also use the cell reference in place of the criterion argument of the COUNTIF function.
Let’s assume we have entered the keyword “IT” in cell F1. Now, taking it as the cell reference to tell the function to look for the keyword “IT” in the defined range, our formula will become,
=COUNTIF(B2:B11,E1)Note that while defining the criteria using the cell reference, you don’t need to use double quotation marks.
EXAMPLE #2 – Using the COUNTIF function to find the cells that contain a portion of the given Text String (Phrase Match)
The COUNTIF function can be combined with the Wildcard characters to find the number of cells containing the portion of a text string.
For example, while counting the cells containing a specific letter or combination of letters.
Here are two popular wildcard characters in Google Sheets,
- “?” – The question mark wildcard is used to count the cells that contain a single character as a part of a string
- “*” – The asterisk wildcard is used to count the cells that contain a sequence of characters at the beginning or end of the string
Now, let’s consider the following example where we have employee data with their department and designation.
Our task is to find the count of the Analysts.
Let’s get started,
- Select the desired cell
- Type “=countif”
- Choose the first option from the popup or press the “Tab” key
- Replace the first argument of the function with the cell range “B2:B11“
- Press “,” on your keyboard to move to the next argument
- Now, for the criteria argument, type “*Analyst*“
(Make sure to use double quotation marks to enclose both the keyword and the wildcard character) - Complete the bracket using “)“
- Press the “Enter” key
Our formula should look like below,
=COUNTIF(B2:B11,"*Analyst*")The function will count the entire content of each cell from the range B2:B11 to find if they contain the keyword “Analyst”.
There are 3 analysts in the above employee data table.
PRECAUTIONS
#1 – Using the Asterisk (*) symbol
You can put this symbol before or after the combination of letters you wish to count.
For example, if you use “A*”, it will start searching for the cells containing the letter A at the beginning of the text string.
Whereas, if you mention “*A” then it will search for the cells containing the letter A at the end of the text string.
#2 – Using the Tilde (~) symbol
In the case of cells containing an asterisk (*) symbol as part of the text string, the function will return an error.
Here, you can use a tilde (~) sign before those characters so that the COUNTIF function will treat the asterisks as symbols and not search.
EXAMPLE #3 – Using the COUNTIF function to find the cells that contain a specific Number (Exact Match)
In the previous example, we used text while defining the criteria. Now, let us see how to use the numbers as the criteria.
We will use the employee data as follows. It contains employee names, departments, and their annual salary.
Our task is to find employees with an annual salary equal to $100,000.
Here are the steps,
- Select the desired cell
- Type “=countif”
- Choose the first option from the popup or press the “Tab” key
- Now, replace the range argument of the function with the “C2:C11“
- Press “,” on your keyboard to move to the next argument
- Type “100000” in the place of the criterion argument
(Make sure to use the double quotation marks) - Complete the bracket using “)“
- Press “Enter” on your keyboard
The final formula should look like below,
=COUNTIF(C2:C11,"100000")As calculated in the above GIF, there are three employees with an annual salary of $100,000.
Note that you just need to type the number without using the “$” symbol as the prefix. That symbol is part of cell formatting. We have formatted column C to reflect the numbers as financial amounts.
EXAMPLE #4 – Using the COUNTIF function to find the cells containing numbers greater than a specific value
To find the cells containing numbers greater or less than a specific value, you need to use Comparison operators.
It’s pretty straightforward.
There are six Comparison operators used in Google Sheets, as follows:
- “=” – Equal To
- “< “- Less Than
- “<=” – Less Than or Equal To
- “>” – Greater Than
- “>=” – Greater Than or Equal To
- “<>” – Not Equal To
They are self-explanatory and the final choice depends upon your specific requirements.
Consider the following table for reference:
| Criteria | Example Formula | Action |
|---|---|---|
| The number is greater than | =COUNTIF(C2:C10, “>10”) | Counting the number of cells where the values are greater than 10 |
| The number is less than | =COUNTIF(C2:C10, “<10”) | Counting the number of cells where the values are less than 10 |
| The number is equal to | =COUNTIF(C2:C10, “=10”) | Counting the number of cells where the values are equal to 10 |
| The number is greater than or equal to | =COUNTIF(C2:C10, “>=10”) | Counting the number of cells where the values are greater than or equal to 10 |
| The number is less than or equal to | =COUNTIF(C2:C10, “<=10”) | Counting the number of cells where the values are less than or equal to 10 |
| The number is not equal to | =COUNTIF(C2:C10, “<>10”) | Counting the number of cells where the values are not equal to 10 |
Please make sure to enclose the Comparison operators along with the number in double quotations (“”).
Now, let’s consider the following table.
We have employee data with their names, city, and age. Our task is to count the employees whose age is above 50 years.
Here are the steps,
- Select the desired cell
- Type “=countif”
- Choose the first option from the popup or press the “Tab” key
- Now, for the range argument, let us put “C2:C11“
- Press “,” to move to the next argument
- For the criterion argument, let us type “>50“
(Make sure to use the double quotation marks; otherwise, the formula will return an error at the end) - Complete the parenthese using the “)“
- Press the “Enter” key
The formula should look like below,
=COUNTIF(C2:C11,">50")The function has calculated that there are 4 employees with the age above 50 years, as seen in the above GIF.
Similarly, if we had to find the number of employees whose age is below 50 years, our formula could be as follows.
=COUNTIF(C2:C11,”<50”)EXAMPLE #5 – Using the COUNTIF function to find the blank cells in a column
The COUNTIF function offers the quickest to count blank and non-blank cells from a specific column.
In the following table, we can see that a few employees couldn’t get the bonus for 2024.
Our task is to find the exact count of employees who couldn’t get the bonus.
Let’s begin,
- Select the desired cell
- Type “=countif” and choose the first option from the popup
- Replace the range argument with “C2:C11“
- Press “,” to move to the next argument
- Now, type “<>” in the place of the criterion argument
(Make sure to use the double quotation marks) - Complete the parentheses using “)“
- Press the “Enter” key
The final formula to count the blank cells is as follows,
=COUNTIF(C2:C11,"<>")What is the difference between the COUNTIF and COUNTIFS functions in Google Sheets?
The COUNTIF function doesn’t support multiple criteria directly within a single function. In other words, users can define a single criteria within the COUNTIF function.
The COUNTIFS function is the advanced version of the COUNTIF function. It allows users to define multiple criteria within a single formula.
Consider an example where we plan to count the cells that satisfy multiple criteria.
The general syntax for the COUNTIFS function is as follows,
=COUNTIFS(range1, criterion1, [range2, criterion2 . . . . ])Where the range1 and range2 arguments need to be replaced with the cell range where we wish to find the cells that meet multiple criteria.
Similarly, you can define multiple criteria in the place of criterion1, criterion2, criterion3, and so on arguments.
EXAMPLE #6 – Using the COUNTIFS function to find the cells that satisfy multiple criteria
We have discussed the COUNTIF function examples in detail in the above sections.
Now, let us have a quick look at one of the common COUNTIFS function examples.
Consider the following table including employee details such as their name, department, and annual salary.
Our task is to find the employees from the IT department with a salary greater than $100,000.
Here are the steps,
- Select the desired cell
- Type “=countifs”
- Choose the first option from the popup or press the “Tab” key
- Replace the range1 argument with the cell range “B2:B11“
(The department is available in column B. So, we will be searching the keyword IT in the cell range B2:B11 of the column B) - Press “,” to move to the next argument
- For the criterion1, let us type “IT“
(Make sure to use the double quotation marks) - Press “,“
- Next, for the range2, let us put “C2:C11” as the range
- Press “,“
- Type “>100000” in the place of the criterion2 argument
- Complete the parenthese using “)“
- Press the “Enter” key
Our final formula should look like below
=COUNTIFS(B2:B11,"IT",C2:C11,">100000")FAQs
Q. How do I use COUNTIF with two criteria in Google Sheets?
The COUNTIF function accepts only one criterion. For multiple criteria, you need to use the COUNTIF function in Google Sheets.
Here’s the general syntax for the COUNTIF function,
=COUNTIFS(range1, criterion1, [range2, criterion2 . . . . ])To know more about the syntax and the steps to use the COUNTIF function, refer to the above section of this article.
Q. How do I count if a cell contains partial text?
It is possible to count the cells containing a portion of the given text string.
You need to use Wildcard characters within the COUNTIF formula. Please refer to the above section of the article to know the detailed steps.
To Summarize: The COUNTIF Function in Google Sheets
It’s effortless to use the COUNTIF function and find how many times a specific value, text, or number has appeared in the given column.
Moreover, you can combine the function with Wildcard characters to find a particular text string that starts and ends with a specific character.
If you are stuck somewhere while practicing the examples discussed above, please feel free to comment below. Our experts reply to all comments within a few hours.
To learn more excellent tips and tricks to become a pro at using Google Sheets, check out our blog page.