Excel: What can you do with the SUMPROD­UCT function?

The name is ad­mit­ted­ly somewhat mis­lead­ing. The function does not give the product (result of a mul­ti­pli­ca­tion) of multiple sums (result of an addition). Instead, the function does the opposite: it ag­gre­gates multiple products to form a total. In Excel, SUMPROD­UCT helps with every­thing from small tables to huge matrices.

Whenever you want to multiply several values in Excel and then aggregate the results, the SUMPROD­UCT 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 SUMPROD­UCT. Let’s assume we have a table with two columns: SUMPROD­UCT lets you multiply the two values in each row with one another and then sum them.

SUMPROD­UCT 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 SUMPROD­UCT because the function is designed for pro­cess­ing matrices. That is why Excel doesn’t require a special command.

The Excel SUMPROD­UCT function has a rel­a­tive­ly simple structure. In general, you only need to specify the range to be included in the cal­cu­la­tion.

The cor­re­spond­ing cell ranges must always be sym­met­ri­cal: For example, if the second array contains one row or a value less than the first range, an error message is displayed.

The SUMPROD­UCT function offers several ad­van­tages. The obvious advantage is that you can save several cal­cu­la­tion steps by linking mul­ti­pli­ca­tion and addition. The SUMPROD­UCT can also be combined with other functions. The function offers several tricks that are in­ter­est­ing for more complex tasks.

For example, SUMPROD­UCT has a built-in element like the SUMIF function. It lets you choose which values in the matrices are to be added to the cal­cu­la­tion. All you have to do is modify the actual syntax of the function somewhat:

We have added a com­par­i­son to the first argument. The relevant cells are only mul­ti­plied and added to the total if the details cor­re­spond to the value in cell A14. Besides the equal sign that performs the com­par­i­son, mul­ti­pli­ca­tion 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 com­par­i­son, the function requires clear iden­ti­fi­ca­tion of the cal­cu­la­tion operation. Otherwise, an error occurs.

You can also use an asterisk for mul­ti­pli­ca­tion. 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. The­o­ret­i­cal­ly, 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 mul­ti­pli­ca­tion symbol again. The values in the large range are first added to one another, and then mul­ti­plied by the second range.

You can use the comma again in this formula. However, you have to tell Excel ex­plic­it­ly that you want to sum the in­di­vid­ual ranges first.

Of course, the SUMPROD­UCT 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. Im­por­tant­ly, arrays will need to be of the same length for this function to work.

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