SumProduct Formula in Excel
Printer Friendly Page
SumProduct multiplies values in two or more cell
ranges and adds together the results.
This table contains sample data as it would appear in a spreasheet.
If we wanted to find the total sales for all products, we could multiply each cell in column C (quantity) by the amount in column D (price).
In other words,
F2 = C2*D2, F3 = C3*D3, etc.
After all the amounts are calculated in column F, we could enter the formula
=SUM(F2:F5) in cell
F7 to get the result, which is
270.
There is an easier way . . .
By using
SUMPRODUCT you can accomplish the same result with out all the calculations we used in the above example.
= SUMPRODUCT(Range, [Range], ....)
This formula can be placed in cell
"E7":
= SUMPRODUCT(C2:C5,D2:D5)
This formula multplies the all the quantities in
Column C by all the prices in the adjacent
Column D.
It then calculates the
SUM of the all the
Products.
We get the same result with one formula.
Up to 30 columns (or ranges) can be multiplied with
SUMPRODUCT
Enter the formula
=SUMPRODUCT(A2:A5,B2:B5,C2:C5,D2:D5) in cell
G7 to get the result.
There is a lot more that can be done with SUMPRODUCT . . .
It is possible to apply conditions to the formula that will act as filters.
For example, if I want to only see the sales amounts for Chicago I can enter the following formula at
F7:
= SUMPRODUCT((A2:A5="Chicago")*(C1:C4))
This checks if any of the cells in
column A equal "Chicago",
and mutliplies the matching rows from
column C.
How this works . . .
In the above example,
A2 and
A4 equal "Chicago".
In Excel,
TRUE evaluates to
1 and
FALSE evaluates to
0.
For every row where Column A = "Chicago", the value is
TRUE or
1.
Where it is not "Chicago", it is
FALSE and therefore
0.
The part of the formuia that is
(A2:A5="Chicago") changes column
A to a column of
TRUE and
FALSE values, or
1 and
0.
In this example, rows 2 and 4 = "Chicago" and therefore rows 2 and 4 = 1.
Rows 3 and 5 will be 0.
The multiplication is
(A2*C2)+(A3*C3)+(A4*C4)+(A5*C5),
or
(1*2500) + (0*3000) + (1* 4000) + (0*1500)
or
2500 + 0 + 4000 + 0 = 6500.
The Multiplication results are added together to return
6500
Multiple Filters . . .
In the example below, we want sales results for
Chicago, but only for product
ABC.
At cell
D7 we enter this formula:
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5))
The above example checks for cells in column
A equal to "Chicago" and for cells in
column
B equal to "ABC" and converts the cells to
TRUE and
FALSE, or
1 and
0.
A2 and B2 match the criteria, so they have a value of
1.
The other rows are false, and thus are
0.
The multiplication is
(A2*C2) + (A3*C3) + (A4*C4) + (A5*C5),
or
(1*1*2500) + (0*0*3000) + (1*0*4000) + (0*0*1500)
or
2500 + 0 + 0 + 0 = 2500.
More Examples . . .
SUMPRODUCT can be used to calculate multiple formulas:
=
SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5="Jan:), (D2:D5), (E2:E5))
will return
10.
Row 2 is
(1 * 1 * 2 * 10 * 1) which is
10.
A SUMPRODUCT Application. . .
This example represents a sample list of data.
This is the type of data that would be imported into Excel from a database.
Below are two formatted reports that can be created with SUMPRODUCT.
One formula is created for cell
B24.
The formula can then be copied to all the other cells.
This is done by careful use of relative and absolute cell references.
Notice the sections of the formula outlined in red and green.
You can add additional lines to the original datalist and the formula will still work.