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.