How to calculate a weighted average with a condition in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)Weight and filter in the top, total the weights for the same rows in the bottom: =SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7).
- Result on the example
- $76.43 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
The top adds price × units for shoe rows only, which is the shoe revenue (41,655). The bottom adds the units sold for the same rows (545). Dividing gives the average price per pair actually sold: $76.43, lower than the plain average because the cheap sandal sold the most.
=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)Result76.43119266
A2:A76 rows × 1 col: "Shoes", "Bags", "Shoes", "Shoes", …A range: 6 cells in one column.
G1"Shoes"The value in G1.
A2:A7=G1{6 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
C2:C76 rows × 1 col: $89, $45, $119, $39, …A range: 6 cells in one column.
A2:A7=G1)*C2:C7{6 rows × 1 col} 89, 0, 119, 39, …The * operator multiplies.
D2:D76 rows × 1 col: 140, 210, 85, 260, …A range: 6 cells in one column.
A2:A7=G1)*C2:C7*D2:D7{6 rows × 1 col} 12460, 0, 10115, 10140, …The * operator multiplies.
SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)41655SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
A2:A7=G1)*C2:C7*D2:D7{6 rows × 1 col} 12460, 0, 10115, 10140, …
- array1
SUMIF(A2:A7,G1,D2:D7)545SUMIF: Adds values where the range meets a condition.- range
A2:A76 rows × 1 col: "Shoes", "Bags", "Shoes", "Shoes", … - criteria
G1"Shoes" - sum_range
D2:D76 rows × 1 col: 140, 210, 85, 260, …
- range
SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)76.43119266The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7). We ran it in Google Sheets mode on the same data and it returns $76.43.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(C2:C7*D2:D7,A2:A7=G1))/SUM(FILTER(D2:D7,A2:A7=G1))Excel only | $76.43 | Excel 365: filter the revenue and the units, then divide. |
| =AVERAGEIF(A2:A7,G1,C2:C7) | $99.00Different result | The unweighted average of the shoe prices. Treats every product equally: $99.00. |
Common mistakes
- The condition must be on both the top and the bottom. Dividing by
SUM(D2:D7)divides shoe revenue by all units, bags included. - If no row matches, the bottom is 0 and the result is #DIV/0!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate a weighted average with a condition
G2, calculate the average price paid per unit for the category in G1, weighting each price by units sold.Questions
How do I add a second condition?
Multiply another test into the top and switch the bottom to SUMIFS with the same two conditions.
How do I do the weighted average without a condition?
Use =SUMPRODUCT(C2:C7,D2:D7)/SUM(D2:D7).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.