Weighted average in Excel with SUMPRODUCT, on customer margins

A weighted average in Excel is =SUMPRODUCT(values, weights)/SUM(weights): multiply each value by its weight, add the products, and divide by the total weight. Excel has no AVERAGE.WEIGHTED function, so SUMPRODUCT is the standard way. On six customers below, the simple average margin is 25.0% and the revenue-weighted margin, the one that describes the business, is 21.17%.

The formula

Weighted average = Σ (value × weight) / Σ weight

In Excel: =SUMPRODUCT(values, weights)/SUM(weights)

SUMPRODUCT multiplies the two ranges row by row and returns the sum of the products. Dividing by the sum of the weights turns that total back into an average. The weights can be any positive numbers, such as revenue in dollars; they do not need to add up to 100%.

Google Sheets has a ready-made AVERAGE.WEIGHTED function. Excel does not, so a workbook built in Sheets with that function shows #NAME? when opened in Excel. SUMPRODUCT works in both.

The data: six customers

Revenue for the year in column B, gross margin in column C (stored as a percentage, so 18% is the number 0.18), and segment in column D. Rows 2 to 7:

Customers A, B and C are the same three accounts as in gross margin vs contribution margin vs net margin, where their margins are built up line by line. The per-account figures come from margin by account.

AVERAGE gives the wrong answer

=AVERAGE(C2:C7)

This returns 25.0%: the six margins add to 150 percentage points, and 150 / 6 = 25.0. Every customer counts once, so customer C, with $12,000 of revenue, moves the result as much as customer A, with $400,000.

That is the wrong figure for any question about the business as a whole. The question "what margin do we make across these customers?" means total gross profit over total revenue, and the small, high-margin accounts pull the simple average up. The general principle, count-weighted against value-weighted, is in count-weighted and value-weighted measures.

SUMPRODUCT gives the right one

=SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7)

This returns 21.17%. To see each product, add a helper column E with =B2*C2, fill it down to row 7, and divide its total by total revenue:

=SUM(E2:E7)/SUM(B2:B7)

Revenue × margin is each customer's gross profit in dollars. The products add to $211,700, and $211,700 / $1,000,000 = 21.17%. The weighted average is 3.83 points below the simple one, because the three largest customers, A, D and F, have the three lowest margins.

The check that proves it

A revenue-weighted margin, multiplied by total revenue, must return total gross profit:

21.17% × $1,000,000 = $211,700

In the sheet, the weighted margin times SUM(B2:B7) must equal =SUM(E2:E7), the total of the helper column: both are 211,700. Run the same test on the simple average and it fails: 25.0% × $1,000,000 = $250,000, which is $38,300 of gross profit that does not exist. A weighted average that describes a total must rebuild that total; one that cannot is weighted by the wrong thing.

The same identity sits behind the company figure in gross profit margin: total gross profit over total revenue is the line margins weighted by revenue share.

Weighted average with a condition

For one segment only, put the condition inside SUMPRODUCT as a TRUE/FALSE term, and divide by the segment's revenue with SUMIFS:

=SUMPRODUCT((D2:D7="Enterprise")*B2:B7*C2:C7)/SUMIFS(B2:B7,D2:D7,"Enterprise")

(D2:D7="Enterprise") returns TRUE or FALSE per row, and multiplying turns those into 1 and 0, so only Enterprise rows reach the sum. The SUMIFS argument order is covered in SUMIF and SUMIFS in Excel.

Enterprise: 164,600 / 838,000 = 19.64%. Mid-market: 47,100 / 162,000 = 29.07%. Weight the two segment margins by their revenue and you get 21.17% again, because the segment gross profits add back to $211,700. That recombination is the second check: segments must rebuild the company figure.

Other finance uses

• Average selling price: price weighted by units sold, =SUMPRODUCT(units,price)/SUM(units), which equals revenue over units.
• Average days to pay: days weighted by invoice value, so one late $200,000 invoice counts for more than ten late $500 ones.
• Weighted average cost of inventory: unit cost weighted by quantity on hand or purchased, one of the inventory cost methods US GAAP allows.
• Price realization: realized price against list price weighted by invoice value, as in price realization by customer.

Where it goes wrong

• Weighting by the wrong thing. A margin is weighted by revenue, a price by units, days to pay by invoice value. Weighting a margin by units mixes cheap and expensive products as if they were the same size.
• Ranges of different lengths. SUMPRODUCT needs arrays of the same dimensions; =SUMPRODUCT(B2:B7,C2:C8) returns #VALUE!.
• Blank or text weights. SUMPRODUCT treats non-numeric entries as zeros, so a revenue figure stored as text drops out of the products silently while the customer may still count elsewhere in the sheet. Check =COUNT(B2:B7) equals the number of rows.
• Averaging averages. The simple average of the two segment margins is (19.64% + 29.07%) / 2 = 24.36%, the original error repeated one level up. Weight segment figures by segment revenue, or recompute from the rows.
• Percentages stored as whole numbers. A margin typed as 18 instead of 0.18 multiplies the result by 100: 2,117% instead of 21.17%.

Margin per customer, weighted correctly

Covirage always weights by the right base, computing totals from the underlying rows rather than averaging percentages, and the tools check that the segment figures recombine to the company figure; the external AI model explains the result and never does the arithmetic. See margin per customer from your ledger, with the weighted total and the accounts that pull it down, in customer profitability. For the simple average, median and spread beside it, see descriptive statistics in Excel.