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