The Excel formula to countif using multiple criteria is none other than =countifs( ). It is towards the end that an s is added, signifying it is plural and there are multiple criteria (2 or higher).
Excel users are never satisfied when it comes to formulas. A powerful function like countif is sometimes not enough.
Apart from the capability to tally and find data on the basis of a huge range of assorted criteria, what users now want is to count data based on multiple criteria specification. It was in Microsoft Excel 2007 that this requirement was fulfilled with the advent of countifs or countif multiple criteria.
What is countif multiple criteria in Excel?
With Microsoft Excel introducing the Countifs function in 2007, long chains of countif in any formula were relegated to the past. Specifically, the formula for the countifs function is:
Countifs (range1, criteria 1 [range 2], [criteria 2],…)> /span>
In this, the range argument[s] define the multiple group of cells that need to be counted.
The criteria argument[s] on the other hand, define the circumstances on the basis of which cells are to be added to the count.
The criteria can range across a number, expression, text string or cell reference.
There are a few rules for using Countifs multiple criteria though. For example, if you use multiple ranges, these do not have to be adjacent, but they must have the same number of columns and rows. Additionally, non-numeric criteria must be in double quotes, while numeric criteria need quotes. Wildcard characters * and ? can also be used in the criteria. But it is important to remember wildcards work with text, but not numbers. The & sign can also be used in arguments to concatenate information from another cell enclosed within the criteria. Criteria are cumulative in that results meet specified criteria in an AND relationship, not an OR one.
Benefits of using countif multiple criteria
There are many benefits of using countifs or countif multiple criteria in Excel. For example, you may want to count the number of times varied and multiple criteria are met. In finance, companies can be selected for fitting a certain profile. Finding out how many companies were over a certain market capitalisation but below an EV or EBITDA multiple or below a PE ratio becomes easier.
How to do countif multiple criteria
Countifs work much like countif except that additional criteria are added and separated by commas.
Here’s how you can do multiple criteria countif:
#1 Document Criteria or Conditions to Test For
This is the first and foremost condition of the use of countifs. You need to understand the range of multiple criteria you will be testing for.
#2 Select the Range
The next step is to type “=countifs(“ following which you need to select the range for the first criteria to be tested on.
#3 Input the Test For the First Criteria
The 3rd step after entering the range is to input the test for the first criteria.
#4 Select the Second Rang
The next step is to select the second range you want to test. It can be that range or a new one.
#5 Input test for Second Criteria
The 5th step is to input the test for the second range and criteria.
#6: Repeat this process as many times as required
You then need to repeat this process for the entire range of multiple criteria you want to include in the countifs calculation
#7 Close and Press Enter
The next and final step is to “)” and close to then, press enter.
As can be seen above, each of the criteria are tested and Excel counts the number of times the result of the test is true. This is how excel uses countif multiple criteria; it takes on from countif which extends the basic count function by allowing Excel to count only those items that meet certain criteria.
Just like the IF and Sumif formula, count and countifs are based on logic. You can even employ tests other than text matching. Other operators can also be used such as =, <, >, <= or >= or even <>. The range is the data we want to count from. The criteria is the argument indicating what we want to count. In case of a cell reference, the entire expression should not be in double quotes. Enclose the comparison operator in double quotes instead and place & between the comparison operator and the cell reference. The use of named ranges can simplify it further. Named ranges create more efficiency when multiple ranges/criteria pairs are used in the countifs function.
