How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)

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.


Download Practice Workbook


6 Ways to Use Nested COUNTIF Function in Excel

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 Excel nested COUNTIF function with other functions, so without further delay, let’s see each method in detail and with the appropriate illustration.

excel nested countif

Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


Method 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

Nesting Two COUNTIF Functions

Eventually, the results should look like the figure shown below.

excel nested countif with two countif functions

Read More: COUNTIF Excel Example (22 Examples)


Method 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

Combining SUMPRODUCT and COUNTIF Functions

Lastly, the results should look like the image shown below.

excel nested countif with SUMPRODUCT and COUNTIF Functions

Read More: Apply COUNTIF Function in Multiple Ranges for Same Criteria


Method 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.

Using IF and COUNTIF Functions

Finally, your output should look like the picture given below.

excel nested countif with if function

Read More: VBA COUNTIF Function in Excel (6 Examples)


Similar Readings


Method 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.

Dataset for Utilizing SUM and COUNTIFS Functions

📌 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

Using SUMIFS and COUNTIFS

Subsequently, the final output should look like the screenshot shown below.

excel nested countif with SUM and COUNTIFS function

Read More: Difference Between SUMIF and COUNTIF Functions in Excel


Method 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.

Employing DATE and COUNTIF Functions

Consequently, this yields the output shown in the figure below.

excel nested countif with DATE function

Read More: COUNTIF Date Is within 7 Days


Method 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

Applying COUNTIF, IF, INDEX, and MATCH Functions

Ultimately, the results should appear in the image below.

excel nested countif with IF, INDEX, and MATCH Functions

Read More: How to Compare Two Columns Using COUNTIF Function (4 Ways)


Similar Readings


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.

COUNTIF with Multiple Criteria in the Same Column

Finally, after completing the above step, the output should be 6 as shown in the screenshot below.

excel nested countif with multiple criteria in same column

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.

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)


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.

Use COUNTIFS with Multiple Criteria in Different Columns

Eventually, the final output should appear in the picture below.

excel nested countif with Multiple Criteria in Different Columns

Admittedly, we’ve skipped some relevant Examples of the COUNTIFS with multiple criteria in different columns which you may explore if you wish.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


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.

Practice Section


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. Lastly, visit ExcelDemy for many more articles like this.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo