Weighted Average With Criteria in Excel Using SUMPRODUCT

A weighted average with criteria in Excel answers questions like "what was the average price we got in the East region, counting each sale by how many units it moved?" A plain weighted average uses every row. Adding a condition means only the matching rows count, in both the top and the bottom of the calculation. SUMPRODUCT handles the top, and SUMIF or SUMIFS handles the bottom. This guide is for analysts, finance staff and anyone preparing for an Excel test.

All the examples use this sales table in A1:D8:

What is a weighted average with criteria?#

A weighted average gives each value an importance, called its weight. Here the values are prices and the weights are units: a sale of 300 units should count more than a sale of 40. The formula is:

weighted average = sum of (value × weight) ÷ sum of weights

"With criteria" means you only include rows that meet a condition, such as Region = East. If weighted averages are new to you, start with the guide to how to calculate a weighted average, which covers the version without conditions.

How to calculate a weighted average with one condition#

Put the condition inside SUMPRODUCT, and divide by SUMIF of the weights with the same condition:

=SUMPRODUCT((A2:A8="East")*C2:C8*D2:D8)/SUMIF(A2:A8,"East",D2:D8)

The result is 9.09 (to two decimal places). Here is how it works, part by part:

1. (A2:A8="East") tests every row and returns TRUE or FALSE: TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE.
2. Multiplying turns TRUE into 1 and FALSE into 0, so non-East rows become zero.
3. C2:C8*D2:D8 is price × units for each row. Only the East rows survive: 1,000 + 2,400 + 600 = 4,000.
4. SUMIF(A2:A8,"East",D2:D8) adds the East units: 100 + 300 + 40 = 440.
5. 4,000 ÷ 440 = 9.0909…

The SUMPRODUCT function guide explains how it multiplies ranges row by row, and the article on SUMPRODUCT with examples shows other uses. To use a cell instead of typing "East", put the region in F2 and write (A2:A8=F2) and SUMIF(A2:A8,F2,D2:D8).

Why AVERAGEIF gives the wrong answer#

AVERAGEIF gives every matching row the same weight, so it ignores how many units each sale moved:

=AVERAGEIF(A2:A8,"East",C2:C8)

This returns 11: (10 + 8 + 15) ÷ 3. But most East units sold at 8, so the true average price per unit is 9.09. The simple average is pulled up by the small sale at 15. Use AVERAGEIF when every row should count equally, as in the guide to averages by group; use the weighted formula when rows have different sizes.

How to use two or more conditions#

For two conditions, multiply a second test inside SUMPRODUCT and switch the bottom line to SUMIFS. For East and Online:

=SUMPRODUCT((A2:A8="East")*(B2:B8="Online")*C2:C8*D2:D8)/SUMIFS(D2:D8,A2:A8,"East",B2:B8,"Online")

Only rows 2 and 6 match. The top line is 1,000 + 600 = 1,600, the bottom is 100 + 40 = 140, and the result is 11.43. Each extra condition is one more bracket in SUMPRODUCT and one more range/criteria pair in SUMIFS. The guide to COUNTIFS and SUMIFS and the SUMIFS function guide explain the pairs.

For OR logic, such as East or West, add the tests instead of multiplying them:

=SUMPRODUCT(((A2:A8="East")+(A2:A8="West"))*C2:C8*D2:D8)/(SUMIF(A2:A8,"East",D2:D8)+SUMIF(A2:A8,"West",D2:D8))

This returns 9.77 (7,520 ÷ 770). Adding works here because a row can't be both East and West, so no row is counted twice.

The comma version with the double negative#

You can also give SUMPRODUCT separate arguments, but then the condition needs a double negative:

=SUMPRODUCT(--(A2:A8="East"),C2:C8,D2:D8)/SUMIF(A2:A8,"East",D2:D8)

This also returns 9.09. SUMPRODUCT treats TRUE and FALSE as zero when they are passed as their own argument, so without -- the top line would be 0. The two minus signs turn TRUE and FALSE into 1 and 0. The article on the double negative in Excel explains why. The multiply version above avoids the issue, which is why many people prefer it.

A helper column version with SUMIFS only#

If SUMPRODUCT feels hard to read, add a helper column and use SUMIFS twice. In E2, type =C2*D2 and fill down to get revenue for each row. Then:

=SUMIFS(E2:E8,A2:A8,"East")/SUMIFS(D2:D8,A2:A8,"East")

This gives the same 9.09: East revenue (4,000) divided by East units (440). Helper columns make each step visible, which helps when someone else checks your file. The same idea works in a pivot table: put revenue and units in the source, then add a calculated field of =Revenue/Units. The pivot sums both before dividing, so each group shows its weighted average.

Common mistakes and #DIV/0!#

Most wrong answers come from a mismatch between the top and bottom of the formula. Check these:

1. Different conditions on top and bottom. If the top filters East and Online but the bottom only East, the result is too small. Use the same conditions in both.
2. Dividing by the count instead of the weights. COUNTIF gives the number of rows, not the total units. Divide by SUMIF of the weight column.
3. Ranges of different sizes. Every range in SUMPRODUCT must cover the same rows, or Excel returns an error such as #VALUE! or #N/A.
4. No matching rows. The weights add up to zero and Excel shows #DIV/0!. Wrap it: =IFERROR(SUMPRODUCT(…)/SUMIF(…),"").
5. Weights stored as text. SUMIF skips numbers stored as text, so the bottom line comes out too small while the multiplication on top may still use them. Convert them to real numbers with VALUE.

Practise it#

This formula combines conditional totals with multiplication across ranges. Practise SUMIF in a live spreadsheet for the bottom line, then practise COUNTIFS and SUMIFS for the multi-condition version. Each lesson checks your formula as soon as you type it. Then try the exercise on this page to find the weighted average price for the East region.