Sum Product Formula in Excel
by Linda Quinn
Close Window
Print Page
= SUMPRODUCT(Range, [Range], ....)
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.
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
Qty |
Price |
|
|
|
|
2 |
Chicago |
ABC |
2 |
10 |
|
|
|
|
3 |
Milwaukee |
XYZ |
3 |
15 |
|
|
|
|
4 |
Chicago |
XYZ |
2 |
5 |
|
|
|
|
5 |
St.Louis |
EFG |
5 |
10 |
|
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
|
|
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,
E2 = C2*D2, E3 = C3*D3, etc.
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
Qty |
Price |
|
|
|
|
2 |
Chicago |
ABC |
2 |
10 |
20 |
|
|
|
3 |
Milwaukee |
XYZ |
3 |
15 |
45 |
|
|
|
4 |
Chicago |
XYZ |
2 |
5 |
10 |
|
|
|
5 |
St.Louis |
EFG |
5 |
10 |
50 |
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
125 |
|
|
|
After all the amounts are calculated in column E, we could enter the formula
=SUM(E2:E5) in cell
E7 to get the result, which is
125 .
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.
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
Qty |
Price |
|
|
|
|
2 |
Chicago |
ABC |
2 |
10 |
|
|
|
|
3 |
Milwaukee |
XYZ |
3 |
15 |
|
|
|
|
4 |
Chicago |
XYZ |
2 |
5 |
|
|
|
|
5 |
St.Louis |
EFG |
5 |
10 |
|
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
125 |
|
|
|
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.
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
Qty |
Price |
Delivery |
Tax |
|
|
2 |
Chicago |
ABC |
2 |
10 |
1 |
.5 |
|
|
3 |
Milwaukee |
XYZ |
3 |
15 |
2 |
1 |
|
|
4 |
Chicago |
XYZ |
2 |
5 |
1 |
.5 |
|
|
5 |
St.Louis |
EFG |
5 |
10 |
1.5 |
1 |
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
180 |
|
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))
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
YTD Sales |
|
|
|
|
|
2 |
Chicago |
ABC |
2500 |
|
|
|
|
|
3 |
Milwaukee |
XYZ |
3000 |
|
|
|
|
|
4 |
Chicago |
XYZ |
4000 |
|
|
|
|
|
5 |
St.Louis |
EFG |
1500 |
|
|
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
6500 |
|
|
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 .
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
YTD Sales |
|
|
|
|
|
2 |
1 |
ABC |
2500 |
|
|
|
|
|
3 |
0 |
XYZ |
3000 |
|
|
|
|
|
4 |
1 |
XYZ |
4000 |
|
|
|
|
|
5 |
0 |
EFG |
1500 |
|
|
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
6500 |
|
|
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
In the example below, we want sales results for
Chicago , but only for product
ABC .
At cell
F7 we enter this formula:
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5))
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
YTD Sales |
|
|
|
|
|
2 |
Chicago |
ABC |
2500 |
|
|
|
|
|
3 |
Milwaukee |
XYZ |
3000 |
|
|
|
|
|
4 |
Chicago |
XYZ |
4000 |
|
|
|
|
|
5 |
St.Louis |
EFG |
1500 |
|
|
|
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
2500 |
|
|
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.
SUMPRODUCT can be used to calculate multiple formulas:
=
SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5), (D2:D5), (E2:E5), (F2:F5))
will return
10 .
Row 2 is
(1 * 1 * 2 * 10 * 1 * .5) which is
10 .
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Region |
Product |
Qty |
Price |
Delivery |
Tax |
|
|
2 |
Chicago (1) |
ABC (1) |
2 |
10 |
1 |
.5 |
|
|
3 |
Milwaukee (0) |
XYZ (0) |
3 |
15 |
2 |
1 |
|
|
4 |
Chicago (1) |
XYZ (0) |
2 |
5 |
1 |
.5 |
|
|
5 |
St.Louis (0) |
EFG (0) |
5 |
10 |
1.5 |
1 |
|
|
6 |
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
10 |
|
Copyright © 2006-2019, LQSystems,Inc. All rights reserved.