How to Use the Combination of COUNTIF and SUMIF in Excel

Formula Breakdown:

• SUMIF(B5:B15,”Apple”,C5:C15): The SUMIF function will go through the B5:B15 range and look from “Apple”. It will sum all the prices associated with “Apple” in the range C5:C15.In this case the summation will be 16.
• COUNTIF($B$5:$B$15,”Apple”): The COUNTIF function will scan through the cells in the range B5:B15 and return the number of occurrence of “Apple” in that cell range. It will be 4.
• SUMIF(B5:B15,”Apple”,C5:C15)/COUNTIF($B$5:$B$15,”Apple”): This one is simply the formula of average price. We have the total number of apples over the total price of the apples. In this instance, it will be $16 by 4 or $4.

Formula Breakdown:

• COUNTIF(B5:B15,B5:B15): This will return an array of values. The COUNTIF function will look for the number of occurrence of “Apple”, “Mango” and “Orange” as it moves from cell B5 to B15 and for each value in each cell it will look for number of occurrences of that value in the B5:B15 range and return the number. The result would be something like this : {4;4;4;3;4;3;4;3;4;4;4}.
• SUMIF(B5:B15,B5:B15,C5:C15)<18): At first, the SUMIF function will look for “Apple”, “Mango” and “Orange” in each cell. In the C5 cell, it will have Apple. So it will sum all the prices of Apple from the C5:C15 range and return $16. Then. it will sum for Mango and return $19.5 and then again it will sum for Apple and return $16 and it will do the same as it move from B5 to B15 and finally return an array like this : {16;19.5;16;14.8;19.5;14.8;16;14.8;16;19.5;19.5}. It will evaluate the values if they are less than $18 or not. Then it will return an array of True and False like this: {True;False;True;True;False;True;True;True;True;False;False}.
• SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<18)/COUNTIF(B5:B15,B5:B15)):The True values will be replaced by 1 and the False values with 0. These array with 1’s and 0’s will be divided by the array returned by the COUNTIF function and will return an array like this: {0.25;0;0.25;0.333333;0;0.333333;0.25;0.333333;0.25;0;0}. Finally, the SUMPRODUCT function will sum these values and will return 2.