= 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

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