SUMPRODUCT

Multiply columns row by row and add the results, with or without conditions. Advanced · 6 min · SUMPRODUCT, Weighted average, Array conditions

SUMPRODUCT multiplies matching items in two or more ranges, then adds up the results. =SUMPRODUCT(C2:C9, D2:D9) gives the same answer as =C2*D2 + C3*D3 + … + C9*D9, with no helper column.

It gets more powerful with conditions. A test like (B2:B9="Bread") produces a TRUE or FALSE for every row, and multiplying turns those into 1s and 0s. Rows that fail the test become 0 and drop out of the total.

Steps

A café has placed its weekly order with your bakery. In G2, work out the order value (each unit price times its quantity, all added up) with a single SUMPRODUCT.
What does an item sell for on average, once you account for how many of each were ordered? In G3, divide the order value by the total quantity. Then compare it with the simple average in G4.
How much of the order is pastry? In G6, multiply a category test by the prices and the quantities: =SUMPRODUCT((B2:B9="Pastry")*C2:C9*D2:D9)
Turn it into a category picker. In G9, use the same pattern, but compare the categories with the cell G8 instead of typed text.

Remember

=SUMPRODUCT(a, b) multiplies row by row, then adds.
SUMPRODUCT ÷ SUM gives a weighted average.
(range="x") makes 1s and 0s that switch rows on and off.
Multiply several tests together to require all of them.

Previous: COUNTIFS, SUMIFS and MAXIFS · Next: VLOOKUP: exact matches