User Rating: 5 / 5     Count Occurrences of Multiple Criteria in a Column, relate each Criteria to a Number and return SUM

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

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

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.

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

Related Formulas - illustrated in the Ebook of Excel Formulas.

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