Counting in Excel is one of the most used functions. Most of the requirements in business are either counting or related to the counting of data. For example, if you are working in a business you may need to know how many people are using a particular brand of shampoo. To count this data, you need to use a counting formula such as COUNT, COUNTIF, COUNTIFS, COUNTA etc. You can also use the pivot table for counting, but learning pivot table takes some effort—whereas the above-mentioned formulas are quite simple.
But what about when you want to use these functions with multiple criteria in Excel? Let’s take a look at how to handle this.
COUNTIF is used to count the number of cells in a range based on a certain criteria. The basic syntax of the COUNTIF is:
In the range field, you need to give the range where you are looking forward to count the number of cells. In the criteria field, you need to provide the criteria. Criteria can be numbers, string, cell references, or expressions. Based on the given criteria, the COUNTIF function will check each cell in the range and count the cells that contain the criteria.
For instance, let’s say you want to count the number of people who get more than $5000 salary in an organization. You can use the COUNTIF formula in the range.
As you can see in the above figure, we have the employee data in the sheet and in the second column, we have the respective salary of each employee. If you need to count the number of employees who are getting more than $5000, you can use the COUNTIF formula by providing the salary column in the range field and “>5000” in the criteria field. The COUNTIF function will count the number of cells that qualify.
Uses of COUNTIF
Count the Number of Cells That Contain Only String
If you need to count the number of cells in a range that contain a string (not numbers, dates, or time), you can use the COUNTIF function with criteria having an asterisk (*). The syntax is:
Count the Number of Cells That Are Not Blank
If you want to count the number of cells that contain text, dates, or numbers, you can use the formula:
This formula counts all the cells that are not blank.
Count the Number of Cells Whose Values Are Greater or Less Than Some Value
To count the cells whose values are greater than a certain specified value, you can use the greater than symbol in the criteria field. For example:
To count the cells whose values are less than a certain value, you can use less than symbol in the criteria field. For example:
Count Number of Cells Whose Values Are Equal or Not Equal to Some Value
To count the cells whose values are equal to a number, you can use the equal to symbol in the criteria field. For example:
To count the cells whose values are not equal to a number, you can use the not equal to symbol in the criteria field. For example:
COUNTIF with Multiple Criteria
If there is more than one range and criteria, you can use COUNTIFS function. It works same as COUNTIF, but is used with multiple criteria.
The syntax of the COUNTIFS is:
=COUNTIF(range 1, criteria1, range 2, criteria 2.. )
This feature is available on Microsoft Excel 2007 and later versions.
You can select multiple ranges and apply the criteria. Based on the different criteria, the final count will be displayed.
Continuing with our previous example, say we need to determine a certain hike in the corporation. Assume that the criteria for the hike is that the salary should be less than $5000, and the attendance of the employee should be greater than 85%.
In the above example, we have the employee name as the data in the first column, salary data in the second column, and attendance data in the third column. Now we are counting the number of employees that have a salary of less than $5000 and an attendance record of greater than 85%. Since the data is mentioned in two different columns, we have to specify the respective range and apply the criteria. This will give us the count of cells that meet all the criteria.
Points to Note
- By default, COUNTIFS applies the ’and’ logic among the different criteria given.
- COUNTIFS gives the count of the number of rows whose cells meet the provided criteria.
- COUNTIFS and COUNTIF will also work if the cells are not contiguous.
- You can also use special characters such as *, &, etc., based on your requirements.
- There is no shortcut for this feature. You have to type the formula starting with the equal to sign, put in the ranges and criteria, and hit the enter key.
OR Criteria in COUNTIF
As COUNTIF uses ‘and’ logic by default, you will need to apply some other method to perform an ‘OR’ operation in COUNTIF. In the above example, if you want to count the number of employees that either have a salary of less than $5000, or whose attendance is greater than 85%, we need to apply ‘OR’ logic here.
The basic syntax of the ‘OR’ logic in COUNTIF is:
=COUNTIF(range_1, criteria_1) + COUNTIF(range_2, criteria_2)
In this example, we are working with the same data which was already used as a COUNTIFS example above. But here, we are using the ‘OR’ logic instead of ‘AND’ (the output of COUNTIFS that has ‘AND’ logic by default is 2; and the output of COUNTIF with ‘OR’ logic is 9).
Here, we are adding two different COUNTIF functions to handle multiple criteria. The output of the first COUNTIF (where the criteria is a salary of less than $5000) is 2, and the output of the second COUNTIF (where criteria is attendance greater than 85%) is 7. In this way, we are able to achieve the OR logic in COUNTIF. And please note that to get the correct answer in this case, we need to subtract the number of people who belong to both the categories.
Although there are numerous applications of COUNTIF and COUNTIFS, these functions are easy to learn and remember. I have given a few examples that you can directly try in Microsoft Excel. Once you complete those examples, you can create your own example by collecting random data from the internet. Once you understand the working of the COUNTIF with multiple criteria and practice enough, you will be able to count cells containing any kind of data!
Happy working with Excel!