How to Create Sum Product in Many Criteria in Excel Formula

The SUMPRODUCT function in Excel is a versatile and powerful tool that can perform various calculations based on multiple criteria. It can compare arrays, conditionally sum and count cells, calculate weighted averages, and more. In this article, we will explain the basic theory and procedures of using the SUMPRODUCT function with multiple criteria, and provide a detailed example with real numbers.

The syntax of the SUMPRODUCT function is:

=SUMPRODUCT(array1, [array2], [array3], ...)

Where array1, array2, etc. are continuous ranges of cells or arrays whose elements you want to multiply, and then add. The minimum number of arrays is 1, and the maximum is 255 in Excel 365 – 2007, and 30 in earlier Excel versions.

The SUMPRODUCT function works by multiplying the corresponding elements in the given arrays, and then adding up the products. For example, if we have two arrays A and B, the SUMPRODUCT function will calculate:

=SUMPRODUCT(A, B) = A1*B1 + A2*B2 + A3*B3 + ...

If we have more than two arrays, the SUMPRODUCT function will multiply the corresponding elements in all the arrays, and then add up the products. For example, if we have three arrays A, B, and C, the SUMPRODUCT function will calculate:

=SUMPRODUCT(A, B, C) = A1*B1*C1 + A2*B2*C2 + A3*B3*C3 + ...

Note that all the arrays in the SUMPRODUCT function must have the same number of rows and columns, otherwise you will get a #VALUE! error. If any array contains non-numeric values, they will be treated as zeros.

Procedures

To use the SUMPRODUCT function with multiple criteria, we need to use the Boolean logic to create arrays of TRUE and FALSE values based on the criteria, and then convert them to 1 and 0 by using the double unary operator (–). For example, if we want to sum the values in column D based on the criteria that column B is “Red” and column C is “Large”, we can use the following formula:

=SUMPRODUCT(--(B2:B10="Red"), --(C2:C10="Large"), D2:D10)

This formula works by creating three arrays:

• The first array is –(B2:B10=“Red”), which compares the values in column B with “Red” and returns an array of TRUE and FALSE values, such as {TRUE; FALSE; TRUE; FALSE; …}. The double unary operator converts the TRUE and FALSE values to 1 and 0, such as {1; 0; 1; 0; …}.
• The second array is –(C2:C10=“Large”), which compares the values in column C with “Large” and returns an array of TRUE and FALSE values, such as {FALSE; TRUE; FALSE; TRUE; …}. The double unary operator converts the TRUE and FALSE values to 1 and 0, such as {0; 1; 0; 1; …}.
• The third array is D2:D10, which contains the values we want to sum, such as {10; 20; 30; 40; …}.

The SUMPRODUCT function then multiplies the corresponding elements in the three arrays, and adds up the products, such as:

=SUMPRODUCT(--(B2:B10="Red"), --(C2:C10="Large"), D2:D10)
= 1*0*10 + 0*1*20 + 1*0*30 + 0*1*40 + ...
= 0 + 0 + 0 + 0 + ...
= 0

The result is 0, which means there are no values in column D that meet both criteria of column B being “Red” and column C being “Large”.

We can use the same logic to apply the SUMPRODUCT function with more than two criteria, by adding more arrays of TRUE and FALSE values. For example, if we want to sum the values in column D based on the criteria that column B is “Red” or “Blue”, column C is “Large” or “Medium”, and column E is greater than 50, we can use the following formula:

=SUMPRODUCT((B2:B10="Red")+(B2:B10="Blue"), (C2:C10="Large")+(C2:C10="Medium"), --(E2:E10>50), D2:D10)

This formula works by creating four arrays:

• The first array is (B2:B10=“Red”)+(B2:B10=“Blue”), which compares the values in column B with “Red” and “Blue” and returns an array of 1 and 0 values, such as {1; 0; 1; 1; …}. The plus sign (+) acts as the OR operator, which means either condition can be true.
• The second array is (C2:C10=“Large”)+(C2:C10=“Medium”), which compares the values in column C with “Large” and “Medium” and returns an array of 1 and 0 values, such as {0; 1; 0; 1; …}. The plus sign (+) acts as the OR operator, which means either condition can be true.
• The third array is –(E2:E10>50), which compares the values in column E with 50 and returns an array of TRUE and FALSE values, such as {FALSE; TRUE; FALSE; TRUE; …}. The double unary operator converts the TRUE and FALSE values to 1 and 0, such as {0; 1; 0; 1; …}.
• The fourth array is D2:D10, which contains the values we want to sum, such as {10; 20; 30; 40; …}.

The SUMPRODUCT function then multiplies the corresponding elements in the four arrays, and adds up the products, such as:

=SUMPRODUCT((B2:B10="Red")+(B2:B10="Blue"), (C2:C10="Large")+(C2:C10="Medium"), --(E2:E10>50), D2:D10)
= 1*0*0*10 + 0*1*1*20 + 1*0*0*30 + 1*1*1*40 + ...
= 0 + 20 + 0 + 40 + ...
= 60

The result is 60, which means there are two values in column D that meet all the criteria: 20 and 40.

Example

To illustrate the use of the SUMPRODUCT function with multiple criteria, let’s consider the following scenario:

We have a table of sales data for different products, regions, and months, as shown below:

We want to calculate the total sales for product A in region X for the first quarter (Jan-Mar). We can use the SUMPRODUCT function with multiple criteria to get the answer, as follows:

=SUMPRODUCT(--(A2:A13="A"), --(B2:B13="X"), --(C2:C13={"Jan","Feb","Mar"}), D2:D13)

This formula works by creating four arrays:

• The first array is –(A2:A13=“A”), which compares the values in column A with “A” and returns an array of 1 and 0 values, such as {1; 1; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0}.
• The second array is –(B2:B13=“X”), which compares the values in column B with “X” and returns an array of 1 and 0 values, such as {1; 1; 1; 0; 0; 0; 1; 1; 1; 0; 0; 0}.
• The third array is –(C2:C13={“Jan”,“Feb”,“Mar”}), which compares the values in column C with the array {“Jan”,“Feb”,“Mar”} and returns an array of 1 and 0 values, such as {1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}. The curly braces ({}) create an array constant, which means the comparison is done with each element in the array.

• The fourth array is D2:D13, which contains the values we want to sum, such as {100; 120; 140; 80; 90; 100; 150; 160; 170; 130; 140; 150}.

The SUMPRODUCT function then multiplies the corresponding elements in the four arrays, and adds up the products, such as:

=SUMPRODUCT(--(A2:A13="A"), --(B2:B13="X"), --(C2:C13={"Jan","Feb","Mar"}), D2:D13)
= 1*1*1*100 + 1*1*1*120 + 1*1*1*140 + 1*0*1*80 + 1*0*1*90 + 1*0*1*100 + 0*1*1*150 + 0*1*1*160 + 0*1*1*170 + 0*0*1*130 + 0*0*1*140 + 0*0*1*150
= 100 + 120 + 140 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0
= 360

The result is 360, which means the total sales for product A in region X for the first quarter (Jan-Mar) are 360.

Other Approaches

There are other ways to use the SUMPRODUCT function with multiple criteria, such as using the asterisk (*) as the AND operator, or using the SUMIFS function instead. Here are some examples of alternative formulas that can produce the same result as the previous example:

• Using the asterisk (*) as the AND operator:

=SUMPRODUCT((A2:A13="A")*(B2:B13="X")*(C2:C13={"Jan","Feb","Mar"}), D2:D13)

This formula works by multiplying the arrays of 1 and 0 values instead of using the double unary operator (–), which acts as the AND operator, which means all conditions must be true.

• Using the SUMIFS function:

=SUMIFS(D2:D13, A2:A13, "A", B2:B13, "X", C2:C13, "Jan") + SUMIFS(D2:D13, A2:A13, "A", B2:B13, "X", C2:C13, "Feb") + SUMIFS(D2:D13, A2:A13, "A", B2:B13, "X", C2:C13, "Mar")

This formula works by using the SUMIFS function, which can sum the values in a range based on multiple criteria. The syntax of the SUMIFS function is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where sum_range is the range of cells to sum, criteria_range1, criteria_range2, etc. are the ranges of cells to evaluate based on the criteria, and criteria1, criteria2, etc. are the criteria to apply. The SUMIFS function can accept up to 127 pairs of criteria ranges and criteria.

The formula above uses three SUMIFS functions, one for each month, and then adds up the results.