SUMPRODUCT

SUMPRODUCT multiplies values in the same position across two or more ranges and adds the results — Microsoft defines it as returning “the sum of the products of corresponding ranges or arrays”. Quantity times unit price for every row, then totaled, is one formula instead of a helper column plus a SUM.

It takes more than two ranges, which is what makes it useful for layered calculations: quantity, price, and margin multiplied row by row gives total profit directly. Its most common named use is a weighted average — multiply values by weights, then divide by the sum of the weights — where the alternative is a helper column that exists only to be summed and deleted.

The ranges must line up, and the documented failure is loud: “the array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.” That is the safe case, because it stops rather than guessing.

A type mismatch can instead leave a plausible result. In its array-argument form, SUMPRODUCT “treats non-numeric array entries as if they were zeros”. With nonnegative quantities and prices, a price entered as text can therefore make the total too low — no error, the right number of rows, and a plausible figure. Check the type of every column before trusting the result. The zero treatment applies only to ranges passed as separate, comma-separated arguments; if the multiplication is written inside a single argument, as in SUMPRODUCT(A2:A10*B2:B10), a text entry returns #VALUE! instead of a quiet undercount.

References:

• SUMPRODUCT function
• SUM function