# How to calculate a weighted average with a condition in Excel

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)`.

**Excel:** `=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Category | Product | Price | Units sold |  | Category | Shoes |
| Shoes | Runner | 89 | 140 |  | Average price paid |  |
| Bags | Tote | 45 | 210 |  |  |  |
| Shoes | Trail | 119 | 85 |  |  |  |
| Shoes | Sandal | 39 | 260 |  |  |  |
| Bags | Backpack | 72 | 120 |  |  |  |
| Shoes | Boot | 149 | 60 |  |  |  |

Formula in G2: `=SUMPRODUCT((A2:A7=G1)*C2:C7*D2:D7)/SUMIF(A2:A7,G1,D2:D7)`

Result: $76.43

## 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.

## Other ways

- `=SUM(FILTER(C2:C7*D2:D7,A2:A7=G1))/SUM(FILTER(D2:D7,A2:A7=G1))`: Excel 365: filter the revenue and the units, then divide.
- `=AVERAGEIF(A2:A7,G1,C2:C7)`: 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!.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/weighted-average-with-a-condition