Excel: What can you do with the SUMPRODUCT function?

The name is admittedly somewhat misleading. The function does not give the product (result of a multiplication) of multiple sums (result of an addition). Instead, the function does the opposite: it aggregates multiple products to form a total. In Excel, SUMPRODUCT helps with everything from small tables to huge matrices.

Whenever you want to multiply several values in Excel and then aggregate the results, the SUMPRODUCT function is ideal. For example, if you have several matrices in your worksheet and you want to add them together, it’s very easy to do so with SUMPRODUCT. Let’s assume we have a table with two columns: SUMPRODUCT lets you multiply the two values in each row with one another and then sum them.

SUMPRODUCT is a matrix formula. Typically, if you want to use a function as a matrix formula, you have to confirm entry of the formula using the keyboard shortcut [Ctrl] + [Shift] + [Enter]. But you don’t have to do that with SUMPRODUCT because the function is designed for processing matrices. That is why Excel doesn’t require a special command.

The Excel SUMPRODUCT function has a relatively simple structure. In general, you only need to specify the range to be included in the calculation.

The corresponding cell ranges must always be symmetrical: For example, if the second array contains one row or a value less than the first range, an error message is displayed.

The SUMPRODUCT function offers several advantages. The obvious advantage is that you can save several calculation steps by linking multiplication and addition. The SUMPRODUCT can also be combined with other functions. The function offers several tricks that are interesting for more complex tasks.

For example, SUMPRODUCT has a built-in element like the SUMIF function. It lets you choose which values in the matrices are to be added to the calculation. All you have to do is modify the actual syntax of the function somewhat:

We have added a comparison to the first argument. The relevant cells are only multiplied and added to the total if the details correspond to the value in cell A14. Besides the equal sign that performs the comparison, multiplication signs have been added. In the normal syntax, the ranges can be separated simply using a comma. Excel often already knows how to handle the data. However, if you include a comparison, the function requires clear identification of the calculation operation. Otherwise, an error occurs.

You can also use an asterisk for multiplication. As already mentioned, the function only works if the ranges specified have the same number of cells. However, you may want to multiply by the same value each time. Theoretically, you would have to use an array with the same value in every cell for this. Instead, you can enter the value just once in one row, and then use the “*” in the formula.

As another example, let’s assume that you want to add various cells and then multiply them by a value and then sum up the results again. There are two ways to implement this using Excel:

You can use the multiplication symbol again. The values in the large range are first added to one another, and then multiplied by the second range.

You can use the comma again in this formula. However, you have to tell Excel explicitly that you want to sum the individual ranges first.

Of course, the SUMPRODUCT function can also be combined with other Excel functions. In this way, you can include multiple steps in a single formula and don’t have to use auxiliary cells to store interim results. For example, you can round the result of the function directly. Importantly, arrays will need to be of the same length for this function to work.

In this example, SUMPRODUCT is nestled inside the ROUND function and serves as the first parameter.