User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Occurrences of Multiple Criteria in a Column, relate each Criteria to a Number and return SUM

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Countif with Multiple Criteria - multiply each occurrence with a value corresponding to the respective Criteria and return SUM

 

 

Cell D7 formula: Count occurrences of "Apple" (C2), "Banana" (C3) & "Pear" (C4) in col A, return SUM of values giving 3 (D2) for each occurrence of "Apple", 6 (D3) for "Banana" and 5 (D4) for "Pear". 

   

 

-----------------------------------------------------------------------------------------------

 

 

Countif Multiple Criteria, Relate Values

           

"Open Image in New Tab" for a full and clear view. 

 

 

Cell D7 formula Counts occurrences of "Apple" (C2), "Banana" (C3) & "Pear" (C4) in col A, and returns SUM of values giving 3 (D2) for each occurrence of "Apple", 6 (D3) for "Banana" and 5 (D4) for "Pear".

 

Uses COUNTIF with Array Criteria and SUMPRODUCT (all criteria are combined in the array). Formula uses cell references for criteria cells C2 / C3 / C4 and for value cells D2 / D3 / D4

 

 

Cell D7 formula: 

  

=SUMPRODUCT( COUNTIF( A2:A20, C2:C4)* D2:D4)  

  

Explanation - Cell D7 formula (Non-Array Formula): 

 

1) COUNTIF(A2:A20, C2:C4): returns an array of numbers - {3;3;4} - representing count of occurrence of each of the 3 criteria of C2, C3 & C4 respectively in column A.

 

2) COUNTIF(A2:A20, C2:C4)* D2:D4: multiplies the count of occurrence of each of the 3 criteria of C2:C4 with the respective D2:D4 values and returns an array of these values - {9; 18;20}. Using SUMPRODUCT returns the SUM of these ie. 47.

  

 

 

Other formula(s): All formulas Count occurrences of "Apple" (C2), "Banana" (C3) & "Pear" (C4) in col A, return SUM of values giving 3 (D2) for each occurrence of "Apple", 6 (D3) for "Banana" and 5 (D4) for "Pear"

 

 

Cell C7 formula: Uses COUNTIF with Array Criteria and SUM (all criteria are combined in the array). Formula uses absolute values for both Criteria and Values. Non-Array formula.

 

Cell C8 / D8 formulas: Both formulas use COUNTIF separately with each Criteria respectively. Cell C8 formula uses absolute values for both Criteria and Values whereas cell D8 formula uses cell references. Both are Non-Array formulas.

 

Cell C9 formula: Uses SUMPRODUCT with Array Criteria (all criteria are combined in the array). Formula uses absolute values for both Criteria and Values. Non-Array formula.

 

Cell D10 formula: Uses SUM / TRANSPOSE with Array Criteria (all criteria are combined in the array). Formula uses cell references for both Criteria and Values. Array formula (Ctrl+Shift+Enter). 

 

 

    

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.