As you know, Excel has much more than you think. With a wide range of features, Excel allows you to use these features every time you need them. The COUNTIF function is one of the functions used for counting cell numbers in a range.
However, did you ever think about what would you do if you need to count more than one criterion?
The COUNTIF function is a blessing in this case because it helps in multiple criteria to count in the same dataset. You can use it in combination with another set separated by a plus sign. Furthermore, this function is helpful when used in curly brackets separated by commas.
Among all functions of Excel, COUNTIF, and COUNTIFS are most likely considered similar functions because of their usage intent for counting cells. Well, the COUNTIF function mainly uses for counting cells with a single condition in one range. On the other hand, the COUNTIFS function is used for different criteria given in the same range or in different ranges as well.
Here, you will get to know how you can use COUNTIF Multiple Criteria in Excel.
How to COUNTIF Multiple Criteria?
For COUNTIF multiple criteria, you can use the following formula:
=countifs()
The addition of “s” makes it plural and it shows that more than one criterion is used here.
COUNTIFS Syntax
Below is the syntax you can use for the COUNTIFS:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
In the syntax given above, the arguments are as:
Criteria_range1: This one is a mandatory argument that helps in defining the first range in which the first critetia1 is needed to be added.
Criteria 1: This argument is also mandatory that helps in setting the condition as a number, cell reference, text string, expression, or any other Excel function. The criteria determine which cells needed to be counted and expressed as 10, “<=32”, A6, “sweets”.
[criteria_range2, criteria2]…: This argument is not mandatory and it contains extra ranges and their linked criteria. Around 127 criteria are feasible to add to the formula.
Microsoft Excel by default shows the argument of the function once you start typing that’s why you don’t need to remember the syntax of the COUNTIF function.
How to Use COUNTIF Multiple Criteria?
Below you will see the easy-to-follow steps to use the COUNTIF function. You will also notice that COUNTIF and COUNTIFS functions are used similarly except for the additional criteria separated by commas.
- First, you need to document the criteria that needed to be counted.
- Now, type “=countifs(“ and choose the range you need to count the first criteria on.
- Add the test for the criteria and choose the second range that needed to test. It is up to you as you can choose the same range or even a different one.
- Add the test for the criteria and repeat the above steps as much as needed.
- Close “)” and press ENTER key.
That’s it!
This is how to COUNTIF multiple criteria in Excel easily.
Things to Remember:
The COUNTIFS function in Excel is helpful to count cells in a single range with single criteria or in multiple ranges with multiple criteria.
Note that all extra ranges should be having the same number of rows and columns as the first range (criteria_range1 argument).
You can add contiguous as well as non-contiguous ranges at the same time.
In criteria, you are allowed to add wildcard characters as well, such as the asterisk (*) or a question mark (?).
Keep in mind that the COUNTIFS function will consider the criteria as a zero value if it is a reference to a blank cell.
