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.

Fig. 1: Simple data set with prices and units sold. The total sales are to be calculated.

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.)

Fig. 2: Simple data set showing the calculation for “Total Sales” using the addition and multiplication operators.

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.

Fig. 3: Simple data set showing the calculation for “Total Sales” using an extra column for “Sales”.

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:

Fig. 4: Simple data set and the location of the Insert Function command.

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”.

Fig. 5: The Insert Function dialog box with SUMPRODUCT selected.

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

Fig. 6: The “Function Arguments” dialog box for SUMPRODUCT.

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.

Fig. 7: “Function Arguments” dialog box with the location of the data from our example have been entered.

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

Fig. 8: The result for our simple data set 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.

Fig. 9: Second example. Same sample data set as seen in the previous article.

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

Fig. 10: Sample data set showing the “Function Arguments” dialog box with array locations entered.

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

Fig. 11: The result for the first calculation using the SUMPRODUCT function.

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.

Fig. 12: The final results, after using the fill handle, with focus on the calculation in location G10.

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.