SumProduct Function Applications

When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.

Click Here To Download A Sample Workbook

I have provided a picture of the worksheet for those who cannot open Excel workbooks:

SumProduct – Example 1

The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.

With SumProduct
=SUMPRODUCT(E:E,F:F)

Without SumProduct
=(E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)+(E6*F6)+(E7*F7)+(E8*F8)+(E9*F9)+(E10*F10)+(E11*F11)+(E12*F12)+(E13*F13)+(E14*F14)+(E15*F15)+(E16*F16)+(E17*F17)+(E18*F18)+(E19*F19)+(E20*F20)+(E21*F21)

SumProduct – Example 2

About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.

In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.

With SumProduct
=SUMPRODUCT((C:C=”Maine”)*(D:D=”Sales”),E:E,F:F)

With SumIfs
=SUMIFS(G:G,C:C,”Maine”,D:D,”Sales”)

Array Formula Sum If
{=SUM(IF((C:C=”Maine”)*(D:D=”Sales”),E:E*F:F))}

SumProduct – Example 3

At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.

SumProduct Formula
=SUMPRODUCT(–(C:C=”Maine”)*–(D:D=”Sales”),E:E,F:F)

Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.

SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.

SumProduct Formula
=SUMPRODUCT(–($C:$C=”Maine”),–($I:$I=”Maine”))

This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.

SumProduct can also compare the value in two columns to count identical values

SumProduct Formula
=SUMPRODUCT(–(E:E=40),–(K:K=40))

Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.

SumProduct – Example 4

The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.

In this example the SumProduct weights the average gross pay based on the count of divisions.

SumProduct Formula – Longhand notation for weighted average
=SUMPRODUCT({780,780,860,504,589,504,350,213,860,589,472.15,176,860,880,443.75,880,472.15,780,840,350},{4,6,4,6,5,6,5,6,5,6,5,5,5,5,5,5,6,4,4,5})/SUM(H2:H21)

SumProduct Formula – Shorthand notation for weighted average
=SUMPRODUCT(E2:E21,F2:F21,H2:H21)/SUM(H2:H21)