Sum Product Formula in Excel

by Linda Quinn

Close Window


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  



=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================
Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.


====================================================================
Want an expert to help with your project?    LQ Systems, Inc.   Business Solutions
====================================================================