FORMULA & SQL / TOTALS
Weighted average.
Tested, not guessed.
The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗
The example rows
| Row | A: Region | B: Rep | C: Amount | D: Order Date | E: Status | F: Customer |
|---|---|---|---|---|---|---|
| 2 | North | Ana | 1,200 | 2026-03-01 | Paid | Jo Smith |
| 3 | South | Ben | 450 | 2026-03-15 | Open | Raj Patel |
| 4 | North | Cy | 300 | 2026-04-02 | Paid | Lee Wong |
| 5 | East | Ana | 980.50 | 2026-04-20 | Paid | Mia Ross |
| 6 | North | Ben | 75 | 2026-05-05 | Open | Jo Smith |
| 7 | West | Dee | 1,640 | 2026-05-18 | Paid | Sam Okoro |
The formula
Excel
=SUMPRODUCT(C2:C7,C2:C7)/SUM(C2:C7)
The sum of the Amount column (C2:C7) times the Amount column (C2:C7) divided by the total of the Amount column (C2:C7).
Run on the example rows:
Result1160.070013992
The SQL
PostgreSQL
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
Run on the example rows:
| weighted_average |
|---|
| 1160.07 |
Other databases
MySQL
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
SQL Server
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
SQLite
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
BigQuery
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
Snowflake
SELECT SUM(amount * amount) / SUM(amount) AS weighted_average FROM sales
Your own data
Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗