Countif Multiple CriteriaHow To Use Countifs in Excel
COUNTIF is a valuable statistical function that allows users to count the number of cells in their worksheet that meets a specific criterion.
This can be an incredibly useful tool, particularly in the financial world, for determining what data meets specific values, such as the number of companies whose stock meets a certain market cap.
However, in many cases, we may want to know what data meets more than one criterion, for example, what stock meets a certain market cap and price.
However, when you need to search based on multiple criteria, you generally would be better off using a slightly different formula, specifically COUNTIFS.
The Excel function COUNTIFS is easily mixed up with the COUNTIF formula, but these formulas are, in fact, different.
COUNTIF can only search based on a single criterion and range, whereas COUNTIFS can evaluate both multiple criteria or ranges.
Let’s take a closer look at the COUNTIF and COUNTIFS formulas and how you can use them to evaluate data based on multiple criteria.
What Are COUNTIF and COUNTIFS?
COUNTIF is a function that allows users to count the number of cells that meet a specific set of criteria, such as the number of times a specific name appears in a list.
The syntax of this function is:
=COUNTIF(Range, Criteria)
The range refers to the cells to be searched, such as A1:A6, which simply means to determine how many cells in the range A1 to A6 contain the search value.
The criteria are the search value that the COUNTIF formula will search for, and this can either be in the form of a specific number, expression, text, or a cell reference which Excel will count within the provided range.
COUNTIFS is the plural version of COUNTIF and works in the same way, except that it can search across multiple ranges as well as with multiple criteria.
When used, it will determine the number of times all of the provided criteria are met within the specified ranges.
The syntax for COUNTIFs is as follows:
COUNTIFS(Range1, Criteria1, [Range2, Criteria2]…)
When using COUNTIFS, the range and criteria will be entered in the same way.
However, this function allows additional ranges and criteria to be entered as well. In fact, up to 127 pairs of ranges and criteria may be entered.
One thing to keep in mind, however, is that any additional ranges must include the exact same number of columns and rows as the first one entered.
When using COUNTIF to evaluate more than one range or criteria, the ranges do not need to be contiguous, but if this is the case, only cells that meet all of the provided conditions will be counted.
How To Count Cells with Multiple Criteria
COUNTIFS can easily be used to count cells using multiple criteria, which all must be satisfied.
Though the criteria and ranges may be unique, each must still be entered as a pair individually.
As an example, consider using the COUNTIFS formula to determine which entries on a list of companies meet certain criteria for both market cap and stock price.
To do this, follow these steps.
- Determine the criteria and range you wish to evaluate. In this case, we are looking for a market cap of more than 500 and a stock price of less than 75.
- Enter the =COUNTIFS formula into a blank cell.
- Input the first range and criteria into the formula and follow it with a comma.
- Enter the second range and criteria into the formula. The range can be the same as the initial range or a new one, but if it is a unique range, ensure it contains the same number of rows and columns.
- If necessary, repeat entering additional ranges and criteria as many times as needed, and when you are done, press enter.
That is all there is to it, and as you can see, it is not that difficult.
The only limitation is that each criterion you enter must be satisfied in order for Excel to count it.
This can be a problem when we are looking to count the number of times that unique criteria appear.
Fortunately, there is another method we can use to accomplish this, and surprisingly it is by using the COUNTIF function.
How To Use Multiple COUNTIF Functions to Count Cells
When you want to count the number of cells that meet any one of multiple criteria, the COUNTIFS formula alone will not suffice.
Instead, we need to use another method, and the easiest way to do this is to simply use multiple COUNTIF functions and add them together.
This same idea can work with the COUNTIFS formula as well in order to evaluate more than one condition as well.
Simply write each COUNTIFS formula with the criteria that must be satisfied and add it to a separate COUNTIF or COUNTIFS function in order to count both.
For example, consider if the investor in our example wanted to know how many of the companies had a market cap greater than 500 or had a stock price below 75.
By inputting a COUNTIF formula for each of the criteria, we want to evaluate, and by adding them, we can judge how many cells meet either of these two conditions. Here is how:
- Enter two =COUNTIF formulas into the blank cell, each with the ranges and criteria to be evaluated, and add them together. In our case, this would be “=COUNTIF($B$2:$B$8,”>500″) + COUNTIF($C$2:$C$8, “<75”).”
- Press “Enter” on your keyboard.
- Excel will perform each of the evaluations separately and add them together to provide you with a total.
As you can see, this is relatively simple as well and can provide you with a means to count using multiple exclusive criteria without requiring each to be satisfied.
How To Use COUNTIFS to Count Cells Between Two Specified Values
One common application of COUNTIFS is to evaluate the number of cells that contain a particular value.
However, the methods we have shown above only judge whether a value is above or below a certain amount, so what if you want to know whether it is between two values?
In this case, you can simply use the COUNTIFS function by entering both conditions as separate criteria.
For example, “=COUNTIFS($B$2:$B$8,”>400″,$B$2:$B$8,”<900″).”
Excel will count the number of cells that meet both of these criteria and fall between the two specified values.
If you would like to include values that are equivalent to the two values, use the greater than or equal to or lesser than or equal to operators “>=” or “<=” to include these results.
Conclusion
As you can see, there are several ways to include multiple criteria when counting cells using the COUNTIF function, and these are some of the easiest.
In most cases, the related COUNTIFS function can allow multiple criteria to be evaluated while offering the same utility, and when you need to count cells when any one of multiple criteria is met, simply adding multiple COUNTIF or COUNTIFS functions together can allow you to get the job done.
No matter what criteria you need to evaluate when counting cells, Excel has a way to let you accomplish it.