Business Solutions: Excel: SumProduct Function
      By: James D. Duncan, CPA   December 2005

Excel has many built-in functions and I usually only find them when I am forced to discover a better way to do something. The function I will show you here is no surprise to anyone who has needed it, but was new to me. The problem was, we were importing data (purchase orders) and needed the total cost. We had the quantity and the price but did not have the extension of the two. Here is sample data.

 

       My first thought was to just add a column, put a formula for multiplying quantity times price, and then sum the column. Upon further thought this was impractical. The data comes in large quantities (700 rows and more), the users would have to add a column, put the formula in and then sum the new column. Another complication is that the quantity and price columns are 30 columns apart and the user cannot see both columns at the same time. This could all be handled in the conversion code, but there should be an easier way. Thus, the search through the Excel functions for something to sum the extensions (product?) of these columns.
       Open the functions window, select All in the function category and then move down to Sum in the Function name list on the right side of the Paste Function widow. I moved through sum, sumif and then sumproduct. The explanation in the bottom of the window tells me that this is just what I need.

       Double click on SUMPRODUCT and the function window will open.

 

        Array1 is the column that has the quantity in it. You can highlight A2 through A5 or key it in the Array1 area. We do the same with Array2, which is the price. The results will be shown in the bottom of SUMPRODUCT window.

 

       Click on the OK button and the results of the function are displayed in the designated cell.



       Now to be more independent and help the user, name the ranges of A2:A8 to Quantity and C2:C8 to Price and change B9 to read =SUMPRODUCT(Quantity, Price).

     

        Now, the Total Cost formula will not have to be adjusted when rows are added. Add some rows, put in some new data and see the Total Cost amount change.
       The moral of the story is that there is always another way to handle a problem. Do not give up, just dig a little deeper. Read the books you have on the shelves and do not get down, look on the Internet, ask your friends. And, as in this case, do not settle for the easiest answer. Do not make the user do the work, use your skill and let the computer do the work. It gets to be a really fun game.

       Get out there and help some organization. Neighbors not had time to mow their yard, well wait till they are gone and mow it for them. Will drive them crazy trying to figure out who did it! Never confess and do not even tell your wife. Ha ha. And I can attest from experience, do not be surprised after you have done this, that you will come home and find that your yard has been mowed and NO one knows anything about it. And you will have a friend for life because you extended help with nothing asked in return.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James