There are 400+ built-in formulas to perform complex calculations in Excel.
It makes Excel the top spreadsheet application of 2024. Such formulas require practice to make the most out of an Excel file.
This is a quick guide about the COUNTIF function in Excel. It is one of the most commonly used functions. The main goal is to count the total number of cells that contain a specific value.
Whether you’re a beginner or an experienced spreadsheet user, the COUNTIF function is an easy-to-use tool for simplifying counting and taking data analysis to the next level.
Let us learn how to use the COUNTIF function in Excel with examples. Ensure reading the article till the end and do not miss any critical information that will create significant calculation errors.
Download The Example Excel Sheet
Before we proceed with the steps, please download the following Excel Sheet containing the datasets used to demonstrate various examples of the COUNTIF function.
Note that practising the COUNTIF function examples discussed below will strengthen your knowledge and skills.
Why should you use the COUNTIF function?
The COUNTIF function in Excel allows users to count cells that meet a specific condition quickly.
Here are a few 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 combined with comparison operators to count cells that meet various conditions. 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 COUNTIF function can be easily combined with arithmetic operators to create complex formulas and perform tedious tasks.
Explained: The COUNTIF Function in the Excel
The COUNTIF function in Excel is used to count the number of cells that meet the specific criteria. It is an easy-to-use function with the following general syntax,
=COUNTIF(range, criteria)Each argument of the above function needs to be replaced with the proper information as follows,
- “range” – Here, users can define one or several cells that they wish to count. It will be tested against the criteria mentioned in the second argument to display the final count.
- “criteria” – This argument includes the condition. It is tested against each cell defined in the Range argument.
Note that the criteria can be anything from a numeric value (integer, decimal, data, etc.) to a text string, including wildcards such as ?, *, ~, etc.
Make sure you enclose the condition or criteria in double quotations while using the COUNTIF function.
How to Use the COUNTIF Function in Excel
Now that we have learned that the COUNTIF function has a simple syntax for counting text, numbers, and blank cells within seconds, let’s discuss a few scenarios to sharpen our knowledge further.
- Counting the cells containing an entire text string
- Counting the cells containing the portion of a given text string
- Counting the cells containing the 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 count the cells that contain a specific Text String (Exact Match)
This is one of the most common scenarios where we are asked to count the number of cells that contain a specific text string.
Let us consider the following table, which includes employee details. The first, second, and third columns have employee names, departments, and annual salaries, as shown below.
Our task is to count the number of employees working in the IT department. Here are the steps:
- Open the Excel sheet
- Click on the desired cell
- Type “=COUNTIF”
- Select the first option from the popup or press the “Tab” on your keyboard
- In place of the range argument, enter the cell range as “B2:B11“
- Put “,” to move to the next argument
- Next, type “IT” in the place of the criteria argument
(Make sure to use the double quotation marks; otherwise, the formula will return an error at the end) - End the formulas with a closing parenthesis “)“
- Press the “Enter” key
The final formula would look like,
=COUNTIF(B2:B11,”IT”)Excel will instantly count the cells containing the text “IT” in the Department column. The above GIF shows that there are 4 total employees working in the IT department.
You can also use the cell reference in place of the text used in the Criteria argument. As seen in the example above, the cell reference B4 holds the “IT Employees” keyword. So you can remove “Employees” from the cell B4 and use it in the formula.
Note that if you mention the criteria using a cell reference, there is no need to mention it in double quotations.
EXAMPLE #2 – Using the COUNTIF function to count the cells that contain a portion of the given Text String (Phrase Match)
Here, you need to leverage Wildcard characters and create a complex formula to find the count of cells containing a portion of a given text string.
Here is a quick list of the most used wildcard characters,
- “?” – The question mark is used to count the cells that contain a single character as a part of a string.
- “*” – The asterisk 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.
Task: To find the number of Analysts.
Let’s begin,
- 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, in place of the criteria argument, type “*Analyst*“
(Make sure to use double quotation marks to enclose both the keyword and the wildcard character) - End the formula using “)“
- Press the “Enter” key to see the result
The final formula would be as follows,
=COUNTIF(B2:B11, “Analyst”)Excel will count the entire content of each cell from the defined range to find if they contain the keyword “Analyst”.
As seen in the above GIF, there are 3 analysts in the above employee data table.
EXAMPLE #3 – Using the COUNTIF function to find the cells that contain a specific Number (Exact Match)
In the previous example, we used a text string in the criteria section. Now, let us see how to use the numbers as the criteria.
Here is an example table with employee names, departments, and their annual salary.
Task: 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 criteria argument
(Make sure to use the double quotation marks) - Complete the bracket using “)“
- Press “Enter” on your keyboard
The final formula would look like,
=COUNTIF(C2:C11,"100000")As seen in the above example, there are five employees with an annual salary of $1,00,000.
EXAMPLE #4 – Using the COUNTIF function to find the cells containing numbers greater than a specific value
You can use Comparison operators to find the cells containing the numbers greater than a certain value.
There are six comparison operators, as explained below:
- “=” – Equal To
- “<“- Less Than
- “<=” – Less Than or Equal To
- “>” – Greater Than
- “>=” – Greater Than or Equal To
- “<>” – Not Equal To
You can choose one of them based on your unique requirements. All of them are easy to interpret. We have explained them with examples in the following table.
| Criteria | Example Formula | Action |
|---|---|---|
| The number is greater than | =COUNTIF(C2:C10, “>100”) | Counting the number of cells where the values are greater than 100 |
| The number is less than | =COUNTIF(C2:C10, “<100”) | Counting the number of cells where the values are less than 100 |
| The number is equal to | =COUNTIF(C2:C10, “=100”) | Counting the number of cells where the values are equal to 100 |
| The number is greater than or equal to | =COUNTIF(C2:C10, “>=100”) | Counting the number of cells where the values are greater than or equal to 100 |
| The number is less than or equal to | =COUNTIF(C2:C10, “<=100”) | Counting the number of cells where the values are less than or equal to 100 |
| The number is not equal to | =COUNTIF(C2:C10, “<>100”) | Counting the number of cells where the values are not equal to 100 |
Always remember to enclose the Comparison operators and numbers in double quotations (“”).
Here is an example table.
Our task is to count the number of employees whose age is above 50 years.
Let’s begin,
- Select the desired cell
- Type “=COUNTIF”
- Choose the first option from the popup or press the “Tab” key
- Now, for the range argument, select the cell range “C2:C11“
- Press “,” to move to the next argument
- In place of the criteria argument, type “>50“
(Make sure to use the double quotation marks; otherwise, the formula will return an error at the end) - Complete the parenthesis using the “)“
- Press the “Enter” key
So, the final formula would look like,
=COUNTIF(C2:C11, “>50”)As seen in the above GIF, the formula calculates that there are 5 employees over the age of 50.
Similarly, the formula for finding the number of employees under 50 would be as follows.
=COUNTIF(C2:C11,”<50”)EXAMPLE #5 – Using the COUNTIF function to find the blank cells in a column
The COUNTIF offers the quickest way to count both blank and non-blank cells from the given column.
Refer to 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 criteria argument
(Make sure to use the double quotation marks) - End the formula with a closing parenthesis “)“
- 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 Excel?
There is only one difference between the COUNTIF and COUNTIFS functions in Excel – the number of conditions they can handle.
For example, the COUNTIFS function can count the number of students who scored above 85 on a test and those who scored above 85 and below 90.
The COUNTIF function in Excel doesn’t work when you have multiple conditions to test. At the same time, the COUNTIFS function returns the number of cells that satisfy various conditions.
The general syntax for the COUNTIFS function is as follows,
=COUNTIFS(range1, criteria1, [range2],[criteria2],..)Where,
- “range1” – Here, you can define the range of cells from which the exact number of cells satisfying the given conditions or criteria needs to be counted
- “criteria1” – It represents the condition or logical expression, including numbers, cell references, text strings, and more, to be tested against the cell range defined in the first argument
The rest of the arguments are optional. They are used to define multiple criteria or conditions and cell ranges for complex data analysis.
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 previous sections.
This is the final example on this list where we will practice the COUNTIFS function, which is capable of handling multiple criteria.
Let us consider the following employee details table, including names, departments, and annual salaries.
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 criteria1, enter “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 parenthesis using “)“
- Press the “Enter” key
Our final formula will be as follows,
=COUNTIFS(B2:B11,"IT",C2:C11,">100000")The above GIF shows that there are a total of 3 employees from the IT department with annual salaries greater than 100,000.
FAQs
Q. How do I use COUNTIF with two criteria in Excel?
The COUNTIF function in Excel is used to test only one criterion.
You cannot test two or multiple criteria using the COUNTIF function. Excel offers the COUNTIFS function to test a range of cells against various criteria.
The general syntax is explained in the previous section of this article. We have discussed how to use the COUNTIF function with examples.
Q. How do I count if a cell contains partial text?
To count the cells containing the given text string, you need to combine the COUNTIF function with wildcard operators.
The complete steps are discussed above with the help of a real-life scenario.
To Summarize: Excel COUNTIF & COUNTIFS Functions
I hope you have learned everything about using both the COUNTIF and COUNTIFS functions in Excel.
The COUNTIF is perfect for counting cells based on a single criterion, whereas the COUNTIFS is used for multiple criteria or conditions. We have also discussed complex scenarios in which we need to combine these functions with Wildcard characters and Conditional operators to obtain the desired count.
Feel free to comment below if you are stuck somewhere or having any particular error while using the COUNTIF and COUNTIFS functions in Excel.
Also, check out our blog page to learn more tips and tricks for becoming a pro at using Microsoft Excel and Google Sheets.