Learn how to use the COUNTIF function in Excel using different criteria via different examples.
The COUNTIF function in Excel is a powerful tool that can quickly and easily count the number of cells in a range that meet a specific criterion. It is categorized under statistical functions. It counts the number of cells in a range that meet set criteria. You can use it as a worksheet function and enter it as part of a formula in a worksheet cell.
- COUNTIF Syntax
- Numeric Criteria
- Text Criteria
- Wildcards in COUNTIF
- Count Booleans
- COUNTIFS Function
- COUNTIFS Syntax
- Points to Remember
- Range (required): The range contains the values you want to count.
- Criteria (required): The condition that determines the count of cells.
- Criterion: The criteria for counting cells with text must be enclosed in quotation marks (“”). (Mandatory). To set criteria, signs such as “=”, “>”, “<” can be used. We can use wildcards to refer to the text.
COUNTIF in Excel counts cells equal to a value, greater than or equal to a value, etc.
1. In the worksheet below, we will calculate – Sales over $10,000, Sales in the Furniture Department, and Sales in China.
Read MS Excel Tutorials
Here we will apply the COUNTIF formula in each calculation and the condition for which we need the result.
- Sales over $10,000
The search range is Column D, which mentions the payouts and departments’ names. The formula counts the equal or greater values in the range D2:D20 and tells us that there are 16 values with that criterion.
Let’s use the COUNTIF function to check the text count according to the given criteria.
- Determine Sales in the Furniture department
Here the search range is Column C which has the name of departments. The formula counts the equal or greater values in the range D2:D20 and tells us there are 4 values with that criterion.
- Find out Sales Performance in China
Here is the search range in Column B with country names. The formula counts the equal or greater values in the range D2:D20 and returns the number of values with that criterion, which is 4. Notice that we haven’t used the title case for China in the formula.
The above image shows the final calculation for the given array.
Explore MS Excel Courses
Wildcards in COUNTIF
The COUNTIF function supports logical operators (>,<,<>,<=,>=) and wildcards (*,?) for partial matching. Wildcards are a set of characters that replace other characters in their place. Two wildcard characters can be used in COUNTIF in Excel:
- Question mark (?): It replaces only one character individually. For example, if you use “S?” as a criterion for counting cells with text, all words that have a letter in place of the question mark will be counted, such as: “Yes”, “I know”, and “Your”.
- Asterisk (*): It replaces a set of characters collectively. If now” S*” is used as the criteria to count cells with text, all words beginning with the letter “S” will be counted, such as: “Soup,” “Saturday,” and “Sound.”
Let’s understand the use of asterisk with an example –
In the below dataset, we intend to count the number of cells with the word “management.”
Step 1. We tried to calculate the count of cells with management using the general COUNTIF formula –
You can see that the count is 0.
Step 2. We will use wild card “*” in our COUNTIF formula to count cells with the given criteria.
Hence, using an asterisk helped us find cells with the text of interest.
Must Read – Most Useful Excel Formulas
COUNTIF in Excel – Count Booleans
You can use the COUNTIF function to count Boolean values – TRUE and FALSE
1. In the below array, we have used the COUNTIF function to count the number of cells that contain the value TRUE.
2. Here, we have used the COUNTIF function to count the number of cells that contain the value FALSE.
COUNTIFS allows counting fulfilling more than one criterion. The COUNTIF function applies when there is a single criterion or a single condition, but if two or more conditions are required, we will need the COUNTIF SET function.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
We have the following table of TL Name, Departments, and Team Members under TL. We want to list several team members for a given department. To achieve this, we will –
- Mention the range for the type B2:B17 (the Type 1 values), then add a comma
- Include the criteria (the cell E3, which has the value “Department”), add a comma
- Specify the range for the second condition C2:C17 (the Team Members under TL values), add a comma
- Mention the criteria (the cell F3, which has the value “1”)
So our formula is –
Now we have to change the criteria in every cell, so we have the below formulae for the respective teams –
You may like – MS Excel interview questions
COUNTIF in Excel – Points to Remember
- COUNTIF requires a range.
- It supports only one condition. To apply multiple criteria, use the COUNTIFS function.
- Text strings should be enclosed in double quotes (“”).
- Wildcard characters “?” and “*” can be used in the criteria.
- COUNTIF supports strings longer than 255 characters.
- COUNTIF returns a #VALUE error when referencing any close workbook.
- The COUNTIF function does not count blank cells.
- COUNTIF is not case-sensitive.
- Wildcard characters cannot replace numbers to be counted.
We hope this article helped you to understand COUNTIF in Excel. You can define any criteria according to your needs, and Excel will do the counting only if that criterion is met. In other words, it counts only the values that meet the criteria.
Read More in Excel
MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel
How do I use Countif in Excel?
To use the COUNTIF Function in Excel, you would need to perform the following steps - Select a cell; Type =COUNTIF; Double click the COUNTIF command; Select a range; Type , (Comma); Select a cell (enter the criteria); Press enter.
How do I use COUNTIF in Excel for multiple columns?
To use COUNTIF in Excel for multiple columns, perform the following steps - Step 1: Enter the criteria you want to test for; Step 2: Type u201c=COUNTIFS(u201c and select the range you want to test the first criteria on; Step 3: Enter the test for the criteria; Step 4: Select the second range you want to test.
What is the difference between COUNTIF and COUNTIFS?
The primary difference between COUNTIF and COUNTIFS functions is that COUNTIF counts cells with a single condition in one range. At the same time, COUNTIFS is designed to evaluate different criteria in the same or different ranges.
What is COUNTIF function in Excel?
The COUNTIF function is one of the statistical functions in Excel that allows us to count the number of cells that meet established criteria. For example, how to count the number of times a product or service appears in a range of cells in a list or relationship of sales.
Download this article as PDF to read offlineDownload as PDF