How to Use Nested COUNTIF Function in Excel: 6 Suitable Methods

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″) →  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

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. {5,1} is the array1 argument which refers to the number of instances returned by the COUNTIF function.
  • Output → 6

Formula Breakdown:

• COUNTIF($B$5:$B$13,B16) →  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 if FALSE. Here, 1 is the logical_test argument, which prompts the IF function to return C16*D16 (value_if_true argument) 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.

Formula Breakdown:

• COUNTIFS($D$5:$D$13, {“On Time”,”Delayed”}) →  counts the number of cells specified by a given set of conditions and criteria. The $D$5:$D$13 cells represent the criteria_range1 argument that refers to the Status, whereas the “<>” indicates the criteria1 argument 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

Formula Breakdown:

• MATCH(B16, $B$5:$B$13,0) → returns the relative position of an item in an array matching the given value. 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. 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. 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,””) → 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