In this article we will focus on introducing Excel’s SUMPRODUCT function. It is also, in part, a continuation of a previous article Excel and Some linear Algebra using Matrix Multiply to Solve Problems. We will use the same data file as the previous article (after going through a simple example first) and illustrate how to use the SUMPRODUCT function to solve that same problem.

Let’s first start by looking at a simple example. We will do a bit of basic math then see how the SUMPRODUCT function works. The spread sheet, see figure 1, has three values for price in cells B2 to B4, and the number of units sold are located in cells C2 to C4. We just want to know the “Total Sales” for the month.

**Using Basic Math Operators:**

We could, most certainty, use the multiply and addition operators to calculate the “Total Sales” (as can be seen in figure 2). This method, although serviceable, would be tedious to set up if we had hundreds or thousands of products. (There will also be a hard limit as to length of the formula. Refer to Excel specifications and limits on the Microsoft site for more information.)

Another option would be to add a column for the purpose of calculating the “Sales” for each product, and then add up those values for the “Total Sales”. As seen in figure 3, the text “Sales” was added into cell D1. The calculations for the sales, in locations D2 to D4, were also entered. Specifically, the formulas are: =B2*C2, =B3*D3 and =B4*D4. For total sales, the cell with the text was moved over to cell C6, and the SUM function was used in cell D6 to get the total. As with the pervious calculation (figure 2), this method is serviceable, but it does require adding a column of data to the spread sheet.

**Using the SUMPRODUCT function:**

Let’s start over with the calculations, and this time we will look at the SUMPRODUCT function to achieve the same results. The function will do the multiplications and addition all in one step; and we’ll use the Insert Function command to help us out.

Refer to figure 4. After selecting cell C6, click on the Insert Function command (or sometimes called the Function wizard); it can be found on the Formula Bar OR in the Formulas Tab:

The Insert Function dialog box will appear, as seen is figure 5. In the “Search for a function” area, type in SUMPRODUCT and click on “Go”. The function name should then appear in the “Select a Function” area. Select it, if it is not already selected, and click on “OK”.

The “Function Arguments” dialog box will appear, and it is ready for the input arguments.

Next, we need to type in, or click and drag over, the ranges needed for each of the arrays. For the first argument, “Array1”, the locations of the values for “Unit Price” are entered, B2:B4. For the second argument, “Array2”, the locations of the “Jan Unit Sales” are entered, C2:C4. Click on “OK” to get the results.

In figure 8 you can see the final result of using the SUMPRODUCT function.

**Using SUMPRODUCT With a Previous Data Set:**

Next, we will use the SUMPRODUCT function with the same example that we used in the previous article: Excel and Some linear Algebra using Matrix Multiply to Solve Problems. This example supposes that we have a fictitious business that sells sweets and cakes. It is the end of April, and we would like to know our Year-To-Date sales for each month. In the example spread sheet (see in figure 9) locations B3 to B8 contain the unit prices for each of the product types, and locations D3 to G8 contain the unit sales for each month. In cell D10 we would like to know the total sales for January. This would be 728 Chocolate Cakes at $3.95 each, 743 Brownies at $3.40 each, and so on. Totals for the other months February to April we want in cells E10 to G10.

With cell D10 selected, we’ll use the “Insert Function” command to help us build the argument list for the SUMPRODUCT function; just as we did in the previous smaller example (see figures 5, 6 and 7).

Click on the Insert Function command, select SUMPRODUCT, and the “Function Arguments” dialog box will open. In that dialog box, refer to figure 10, for the first argument, “Array1”, type in, or click and drag over, the range B3:B8. These are the unit prices. Note that for this range we will need to use absolute referencing, more specifically, a $ needs to be in front of the row and column labels. This is needed since we will be using the fill handle to complete the sequence of calculations for E10 to G10, i.e., the total sales for each of the other months. (For more details on using absolute referencing see the article on Absolute vs Relative Addressing). For the second argument, “Array2”, type in, or click and drag over the numbers for the January unit sales, cells D3:D8

After clicking on “OK” the total sales for January will be shown – as see in figure 11.

From here will simply use the fill handle to complete the sequence of calculations for the other months, i.e., click and drag the fill handle over E10, F10 and G10. The final results can be seen in figure 12.

In the previous article we looked at this data set using matrix multiply function MMULT to calculate the total sales per month. Here we looked at the SUMPRODUCT to do the same calculation. This method doesn’t require any linear algebra knowledge and is relatively straight forward to use.

## Leave A Comment