Certainly, the heart and soul of an Excel spreadsheet are its functions, and typically, we nest multiple functions to analyze and organize data. Keeping this in mind, we’ll explore 8 handy ways how to use nested COUNTIF in Excel in this article. In addition, we’ll also learn how to apply the COUNTIF and COUNTIFS functions with multiple criteria in single and double columns respectively.
First of all, let’s consider the Fruit Sales dataset shown in the B4:D13 cells, which contains the Item Name, Quantity, and Unit Price respectively. Here, we want to combine the Excel nested COUNTIF function with other functions, so without further delay, let’s see each method in detail and with the appropriate illustration.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Nesting Two COUNTIF Functions
First and foremost, let’s start with a simple example of an Excel nested COUNTIF function (the OR criteria). Here, the COUNTIF function counts the number of instances where the Quantity is greater than 25 or the Unit Price exceeds $1.50.
📌 Steps:
- First, go to the D16 cell >> enter the formula given below.
=COUNTIF(C5:C13,">25")+COUNTIF(D5:D13,">1.5")
For instance, the C5:C13 and D5:D13 ranges refer to the Quantity and Unit Price columns respectively.
Formula Breakdown:
- COUNTIF(C5:C13,”>25″) → counts the number of cells within a range that meet the given condition. Here, the C5:C13 cells represent the range argument that refers to the Quantity, while the “>25” indicates the criteria argument that returns the count of the values greater than 25.
- Output → 5
- COUNTIF(D5:D13,”>1.5″) → In this case, the D5:D13 cells represent the range argument that refers to the Unit Price, while the “>1.5” indicates the criteria argument that returns the count of the values greater than $1.5.
- Output → 4
- 5 + 4 → 9
Eventually, the results should look like the figure shown below.
Read More: How to Use IF and COUNTIF Functions Together in Excel
2. Combining SUMPRODUCT and COUNTIF Functions
On the other hand, we can also apply the OR criteria by combining the SUMPRODUCT and COUNTIF functions to return the number of occurrences within the specified criteria i.e. Unit Price less than $1.50 or greater than $2.00. In this case, the COUNTIF function counts the number of values, and the SUMPRODUCT function returns the sum of the arrays.
📌 Steps:
- In the first place, move to the D16 cell >> type in the expression given below.
=SUMPRODUCT(COUNTIF(D5:D13,{"<1.50",">2.00"}))
In this situation, the D5:D13 cells represent the Unit Price column.
Formula Breakdown:
- COUNTIF(D5:D13,{“<1.50″,”>2.00″}) → counts the number of cells within a range that meet the given condition. Here, the D5:D13 cells represent the range argument that refers to the Unit Price, while the {“<1.50″,”>2.00″} indicates the criteria argument that returns the count of the values in between the range $1.5 to $2.00.
- Output → {5,1}
- SUMPRODUCT(COUNTIF(D5:D13,{“<1.50″,”>2.00″})) → becomes
- SUMPRODUCT({5,1}) → returns the sum of the products of corresponding ranges or arrays. Here, {5,1} is the array1 argument which refers to the number of instances returned by the COUNTIF function.
- Output → 6
Lastly, the results should look like the image shown below.
Read More: How to Use COUNTIF and COUNTA Functions Together in Excel
3. Using IF and COUNTIF Functions
Alternatively, we can also combine the popular IF and COUNTIF functions to perform simple calculations based on the output returned by the COUNTIF function. Now, allow us to demonstrate the process in the steps below.
📌 Steps:
- Initially, navigate to the D18 cell >> copy and paste the equation given below.
=IF(COUNTIF($B$5:$B$13,B16),C16*D16,"Not Available")
Specifically, the B16, C16, and D16 cells point to Avocados, 25, and $2.00 respectively.
Formula Breakdown:
- COUNTIF($B$5:$B$13,B16) → here, the $B$5:$B$13 cells represent the range argument that refers to the Item Name, while the B16 cell indicates the criteria argument that is Avocados.
- Output → 1
- IF(COUNTIF($B$5:$B$13,B16),C16*D16,”Not Available”) → becomes
- IF(1,C16*D16,”Not Available”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, 1 is the logical_test argument which prompts the IF function to return C16*D16 (value_if_true argument) otherwise it returns “Not Available” (value_if_false argument).
- Output → $50.00
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
Finally, your output should look like the picture given below.
Read More: How to Use the Combination of COUNTIF and SUMIF in Excel
4. Utilizing SUM and COUNTIFS Functions
For one thing, we can utilize the SUM function in conjunction with the COUNTIFS function to count the number of times a value appears based on text criteria. In this situation, the COUNTIFS function computes the cells that match the condition (“On Time”,”Delayed“, or “Not Available”), and the SUM function returns the total count.
Considering the Product Sales and Delivery Status dataset shown in the B4:D13 cells, which contain the Product name, Sales in USD, and delivery Status respectively.
📌 Steps:
- To begin with, jump to the D16 cell >> insert the expression into the Formula Bar.
=SUM(COUNTIFS($D$5:$D$13, {"On Time","Delayed"}))
On this occasion, the D5:D13 cells indicate the delivery Status column.
Formula Breakdown:
- COUNTIFS($D$5:$D$13, {“On Time”,”Delayed”}) → counts the number of cells specified by a given set of conditions and criteria. Here, the $D$5:$D$13 cells represent the criteria_range1 argument that refers to the Status, whereas the “<>” indicates the criteria1 argument that represents the criteria On Time and Delayed.
- Output → {4,3}
- SUM(COUNTIFS($D$5:$D$13, {“On Time”,”Delayed”})) → becomes
- SUM({4,3}) → adds all the numbers in a range. Here, {4,3} is the number1 argument which refers to the output returned by the COUNTIFS function.
- Output → 7
Subsequently, the final output should look like the screenshot shown below.
Read More: Difference Between SUMIF and COUNTIF Functions in Excel
5. Employing DATE and COUNTIF Functions
Furthermore, we can also nest the DATE function inside the COUNTIF function to obtain a value that matches the given date criteria. Here, the DATE function stores the given date, while the COUNTIF function returns the number of matches for this date.
📌 Steps:
- At the very beginning, enter the following into the D16 cell.
=COUNTIF($D$5:$D$13,DATE(2022,1,15))
For example, the D5:D13 range of cells represents the Date column.
Consequently, this yields the output shown in the figure below.
Read More: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
6. Applying COUNTIF, IF, INDEX, and MATCH Functions
Last but not least, by applying the COUNTIF, IF, INDEX, and MATCH functions, we can make a versatile formula to calculate the Total Sales based on the specified condition. In this case, the COUNTIF function checks if the condition holds, then the INDEX-MATCH functions extract the Quantity and Unit Price values, and lastly, the IF function returns the results based on the condition.
📌 Steps:
- To start with, enter the D16 cell >> type in the following expression
=IF(COUNTIF($B$5:$B$13, B16),INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0))*INDEX($D$5:$D$13, MATCH(B16, $B$5:$B$13,0)), "")
Here, the B16 cell points to the condition Lemons.
Formula Breakdown:
- MATCH(B16, $B$5:$B$13,0) → returns the relative position of an item in an array matching the given value. Here, B16 is the lookup_value argument that refers to the Condition Lemons. Following, $B$5:$B$13 represents the lookup_array argument from where the value is matched. Lastly, 0 is the optional match_type argument which indicates the Exact match criteria.
- Output → 5
- INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0)) → becomes
- =INDEX($C$5:$C$13,5) → returns a value at the intersection of a row and column in a given range. In this expression, the $C$5:$C$13 is the array argument which is the Quantity. Lastly, 5 is the row_num argument that indicates the row location.
- Output → 62
- IF(COUNTIF($B$5:$B$13, B16),INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0))*INDEX($D$5:$D$13, MATCH(B16, $B$5:$B$13,0)), “”) → becomes
- IF(2,62*1.3,””) → Here, 2 is the logical_test argument which prompts the IF function to return 62*1.3 (value_if_true argument) otherwise it returns “” (Blank) (value_if_false argument).
- Output → $80.60
Ultimately, the results should appear in the image below.
How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
If you’re looking for ways to use the COUNTIF function with multiple criteria in the same column, then the following section answers this exact question. For instance, we want to find the number of Sales between $1000 and $2000 respectively. So, just follow along.
📌 Steps:
- First, insert the equation in the D16 cell as shown below.
=COUNTIF($C$5:$C$13, ">1000")-COUNTIF($C$5:$C$13,">2000")
In this situation, the C5:C13 array represents the Sales column.
Finally, after completing the above step, the output should be 6 as shown in the screenshot below.
Here, we’ve skipped some of the relevant Examples of the COUNTIF function with multiple criteria in the same column, which you might explore if you want.
How to Use COUNTIFS with Multiple Criteria in Different Columns in Excel
In a similar style, we can also employ the COUNTIF function with multiple criteria in different columns. In this scenario, we want to obtain the number of instances where the Quantity exceeds 50 or the Unit Price is above $1.9.Therefore, let’s see it in action.
📌 Steps:
- In the first place, navigate to the D16 cell and insert the expression below.
=COUNTIFS($C$5:$C$13,">50")+COUNTIFS($D$5:$D$13,">1.9")
In the above expression, the C5:C13 and D5:D13 arrays refer to the Quantity and Unit Price.
Eventually, the final output should appear in the picture below.
Admittedly, we’ve skipped some relevant Examples of the COUNTIFS with multiple criteria in different columns which you may explore if you wish.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
In short, this article shows 8 effective methods for how to use nested COUNTIF in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
