COUNTIFS with Multiple Criteria (Table of Contents)
Introduction to COUNTIFS with Multiple Criteria
Excel provides us COUNTIFS function to return a count if certain conditions are met. The function purely works for multiple conditions. This technique is quite useful in complex calculations that involve a huge dataset. COUNTIFS takes multiple parameters as input, but the parameters are of two types only, viz. condition value and range in which condition value is to be searched.
How to Use COUNTIFS Function in Excel?
Now, we will go through a step-by-step procedure to implement and use countifs with multiple criteria in Excel. For this demonstration, we are going to use Volume distributed to various regions of India. Volume is a measure present in the dataset, which has been given by Region, Mode, and Product dimensions. Mode essentially refers to the distribution mode. We’ll use countifs to return the count based on multiple criteria. Let’s go through the following section to understand this.
- First, have a look at the data, which is as shown in the following screenshot. Observe the dimensions and the measure present in the dataset.
- As we can see, going through the dataset, we have three dimensions which form three criteria columns. We may want to have a count based on the criteria column is shown above. Now, imagine we want to count for conditions as shown by the following table.
- As we can see above, we want to know the count based on the criteria represented by the two columns. Here, by criteria, to give an example, we essentially mean that count the number of entries for which Region is Ahmedabad, and Mode is M1. This task seems to be difficult, but Excel provides us with a very easy approach through the COUNTIFS function. Let’s first see how the COUNTIFS function works.
As can be seen in the above screenshot, when we enter the countifs function, Excel automatically shows what parameters need to be passed into the function. The COUNTIFS function takes multiple criteria ranges and corresponding criteria. The first criteria range and criteria appear as criteria range1 and criteria1.
- We will use the COUNTIFS function now. For this, let’s pass the input parameters properly. The first parameter is the criterion range. Here, the criteria range is the range in the table in which we want to search for counting. In this case, the first criteria range would be the Range column, and the criterion would be the name of the region which needs to be searched in the range. The criterion has to be selected from the table in which we want the value. The following screenshot shows how the first criterion range and criterion are passed as parameters in the table.
Do not just forget to make the criteria range constant (append $ sign in the range), as can be seen in the above screenshot.
- We also have another criterion to be met. This is pertaining to mode. For this, follow the same procedure that we followed for the first criterion except that we would be selecting the Mode column and the criterion range2 from the dataset, and the corresponding value for mode in the other table as the criterion. The implementation is as shown by the following screenshot.
- Now, once done, close the function and press enter. Observe if we get the correct result. As can be seen in the below screenshot, the COUNTIFS function has returned the value 4, which shows that for region Ahmedabad, mode value M1 appears four times. This is correct, as can be seen going through the dataset. Now, let’s implement this function for other region values in the table. For this, copy the function for these entries. Doing this, we get the corresponding count values, as can be seen in the following table.
- So, we find that the COUNTIFSS function makes the seemingly difficult task very easy. The above example we saw had to criteria. Now, we also have a third dimension in the table, which is Product. Now, let’s imagine we want to identify the number of entries with a particular region that used one of the two modes for a certain product. So, this necessarily means that we have three criteria. Well, the implementation of COUNTIFS won’t much differ from what we saw previously. We shall now have third criteria range and corresponding criteria that shall be related to the Product dimension. The table containing criteria is as shown below. We want to return count based on these criteria.
- The following screenshot shows the COUNTIFS function’s implementation to return count based on three conditions, as shown in the above table. Observing the screenshot, we can say that it is just an extended version of the COUNTIFS implementation that we saw previously.
- Now, when the function is implemented, we get the value 1, which means we have 1 such entry that meets these three criteria. Let’s copy the function across all the desired cells. Doing this, we get the result which is shown by the following table.
As we can see above, for certain entries, we have got zero. This means that there exist no such entries in the dataset. For the remaining entries, we got value 1, meaning that such combinations are present.
Things to Remember
- The COUNTIFS function in Excel must be used only when there are two or more criteria. For dealing with a single condition, Excel provides other mechanisms.
- While implementing the COUNTIFS function, it must be noted that criterion range and criterion are selected from correct sources else the result would be incorrect.
Recommended Articles
This has been a guide to COUNTIFS with Multiple Criteria. Here we discuss How to use COUNTIFS with Multiple Criteria along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
