SUMPRODUCT and weighted report metrics

Some totals need a calculation on each row before the rows are added. Inventory value is quantity times price. Base payroll is hours times rate. SUMPRODUCT handles that pattern without first creating a helper column.

Multiply aligned rows

In the inventory dataset, stock quantities are in C2:C7 and prices are in E2:E7. Total retail stock value is:

=SUMPRODUCT(C2:C7,E2:E7)

The function multiplies the first quantity by the first price, does the same for every other pair, and adds the products. The ranges need the same number of cells and matching row alignment.

Avoid multiplying the totals

SUM(C2:C7)*SUM(E2:E7) multiplies every quantity by the combined prices. That is not the value of the individual items. Thinking through two rows by hand makes the distinction easier to see.

Weighted rates use the right denominator

For a marketing report, the overall conversion rate is total customers divided by total visits. Averaging each channel’s conversion rate equally ignores traffic volume. If customers are in D and visits in B, use:

=SUM(D2:D6)/SUM(B2:B6)

This particular weighted metric does not need SUMPRODUCT because the source already contains the counts. Choose the simplest formula that matches the available data.

Acquisition cost

Spend per customer is total spend divided by acquired customers. It measures acquisition cost, not profit. Compare the same channels and time periods in both the numerator and denominator. Keep ratios as numbers until the final display step.

Transfer the skill

The Merchant’s Report and Campaign Council projects ask you to combine totals, row calculations, and ratios. Download their practice files and explain each metric in a sentence. Being able to defend the denominator is as useful as remembering a function name.

Continue your adventure

Use the formula spellbook for syntax or explore the full quest map. You can practice in the browser and download workbooks without creating an account.