How to Find a Weighted Average in Excel with SUMPRODUCT

To find a weighted average in Excel, multiply each value by its weight, add those products, then divide by the total weight. With scores in B2:B4 and their weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). For scores of 90, 80, and 70 weighted at 20%, 30%, and 50%, the result is 77. This method works for grades, prices, survey results, and other values where some items count more than others.

What You’ll Need

A Microsoft Excel workbook. These instructions are for Excel for Microsoft 365 on Windows; the same formula works in current Excel for Mac and Excel for the web.
One column or row of values to average, such as scores, unit prices, or ratings.
A matching column or row of weights that says how much each value should count.
Weights expressed consistently, either as percentages or as numeric proportions. Their total does not have to be exactly 100% because the formula divides by the sum of the weights.

Step-by-Step

Decide what each value represents. A weighted average gives some observations more influence than others. For example, a final course grade might combine a midterm, project, and final exam, with the exam counting more than a short quiz. List the values you want to average and make sure they are comparable: do not mix percentages with raw points unless you first convert them to a common scale.
Set up matching values and weights. Put each value on a separate row in one column, and put its corresponding weight on the same row in another column. For example, enter “Score” in B1 and “Weight” in C1. Enter 90, 80, and 70 in B2:B4, and enter 20%, 30%, and 50% in C2:C4. Each score must stay beside the weight that belongs to it. If you sort or rearrange the data, move both columns together.
Check how the weights are stored. Excel stores a percentage such as 20% as the decimal value 0.2. You can type 20% directly, or type 0.2 and apply Percentage format. You can also use whole-number weights such as 20, 30, and 50. Either approach gives the same weighted average when all weights use the same scale. Do not enter 20 to mean 20% in one row and 0.3 to mean 30% in another; that mixes scales and changes the result.
Choose a cell for the answer. Click an empty cell outside the data, such as D2. Keeping the answer separate from the input ranges makes the worksheet easier to inspect and helps prevent the formula from including its own result. Add a nearby label such as “Weighted average” if the sheet will be used by someone else.
Enter the SUMPRODUCT formula. With scores in B2:B4 and weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) in D2, then press Enter. SUMPRODUCT multiplies each score by the weight in the same position and adds the products. SUM adds the weights, so dividing by that total normalizes the result. With 90 at 20%, 80 at 30%, and 70 at 50%, the numerator is 18 + 24 + 35 = 77 and the weights total 1, so the answer is 77.
Check the result against the example logic. The answer should be closer to 70 than to 90 because the lowest score has the largest weight, 50%. It should also fall between the smallest and largest scores when all weights are nonnegative and the total weight is greater than zero. If Excel displays 0.77, the calculation may be right but the output cell is formatted as a percentage; change its format to General or Number to display 77.
Format the result for the type of data. Select the result cell and use Home > Number to choose an appropriate format, such as Number with one or two decimal places. If the values are percentages, format the result as Percentage. Formatting changes how the answer appears, not the underlying calculation. Avoid using a percentage format for a score recorded as 77 out of 100 unless you specifically want Excel to show 7700%.
Expand the calculation when you add more rows. If you add another value and weight, update both ranges so they cover the same rows. For values in B2:B5 and weights in C2:C5, edit the formula to include B5 and C5 as well. You can select the formula cell and edit the references in the formula bar, or double-click the cell and change the ranges. Check that the new pair is included once, and that the two ranges still have equal lengths.
Optionally use an Excel Table for changing data. For a list that will grow regularly, select the headers and data, then choose Insert > Table and confirm that the table has headers. Tables expand as you add rows, which makes ongoing calculations easier to maintain. You can still use the basic cell-range formula, but remember to extend its ranges when the table grows. In a structured-reference formula, use the actual column names in your table and verify the formula after entering it; column names with spaces require Excel’s standard structured-reference syntax.

Common Mistakes

Using a regular average instead. AVERAGE treats every value equally, so it does not account for different levels of importance. If weights are meaningful, use SUMPRODUCT divided by the sum of the weights.
Leaving out the denominator. SUMPRODUCT alone returns the sum of the weighted values, not their weighted average, unless the weights already add up to exactly 1 and that is intentionally guaranteed. Dividing by SUM of the weights makes the formula work even when weights total 100, 10, or another positive number.
Pairing the wrong values and weights. SUMPRODUCT matches cells by position. If B2 is a test score, C2 must contain that test’s weight. A shifted or sorted weight column can produce a plausible-looking but incorrect answer.
Using unequal range sizes. The values and weights must contain the same number of corresponding cells. For example, B2:B10 has nine cells, so it should not be paired with C2:C9, which has eight. Equal-height ranges prevent a dimension mismatch.
Mixing percentage and whole-number scales. Weights of 20%, 30%, and 50% work together, as do 20, 30, and 50. Mixing 20%, 30, and 50 makes one weight far smaller than the others. Standardize the weight format before relying on the answer.
Including a zero or blank weight by mistake. A zero weight deliberately gives a value no influence; a blank or mistakenly omitted weight may not reflect your intended model. Review the source data rather than assuming every empty cell should count as zero.

Tips

Keep weights in a separate, labeled column. Labels such as “Weight” and “Score” help prevent accidental swaps and make the calculation easier to audit. Note whether weights are percentages or points if other people will use the sheet.
Validate the total weight. Add a temporary check cell or inspect the sum of the weight range. If your weights are intended to total 100%, a sum of 100% or 1 is expected; if you use whole-number weights, a total of 100 is expected. The weighted-average formula still normalizes other positive totals, but a surprising total can reveal a missing or duplicated weight.
Use consistent precision. Keep the underlying values at their actual precision and use cell formatting to control displayed decimals. Rounding each score or weight before calculating can introduce small differences, especially in a long list.
Document assumptions for grades or ratings. A weight might mean a share of the final result, a number of credits, or a frequency count. Make clear what it means. If categories have different maximum scores, convert them to a common scale before combining them unless your grading rules specify another method.
Understand zero and negative weights. A zero weight contributes nothing but remains harmless if the total of the other weights is positive. Negative weights are unusual for ordinary averages and can make the result fall outside the range of the input values, so confirm the logic before using them.

Troubleshooting

If Excel returns #DIV/0!, the sum of the weights is zero or the formula points at an empty or incorrect range. Check that the weight cells contain numeric values and that their total is greater than zero. A blank list of weights cannot produce a meaningful weighted average.

If Excel returns #VALUE! or a formula error, check for text in the value or weight cells, mismatched range sizes, or a typo in the formula. Make sure the formula begins with an equals sign and that the ranges use the intended columns. If your Excel regional settings use semicolons as argument separators, replace the commas with semicolons; this depends on system settings and is not a different calculation.

If the result is unexpectedly high or low, calculate the numerator and total weight separately in temporary cells, then inspect the value-weight pairs row by row. Confirm that percentages were entered consistently and that no row was omitted or included twice. Also check for hidden rows: SUMPRODUCT includes values in hidden rows, so hiding a record does not exclude it from this calculation.

If the answer displays as a date, ####, or an unexpected percentage, select the result and change its number format using Home > Number. A column that is too narrow can show #### for some number formats; widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. The displayed format usually does not mean the underlying formula is wrong.

Frequently Asked Questions

Can I calculate a weighted average when the weights do not add up to 100%?
Yes. The formula divides by the sum of the weights, so weights of 2, 3, and 5 produce the same result as 20%, 30%, and 50%. The total must be positive, and the weights must all use a consistent scale.

What is the difference between SUMPRODUCT and AVERAGE?
AVERAGE gives each numeric value equal influence. SUMPRODUCT multiplies matching values and weights, and dividing by the weight total gives each value the influence specified by its weight.

Can I use SUMPRODUCT if some values are blank?
You can, but decide what a blank means. If it means the value is missing and should be left out, exclude that row from both the numerator and the weight total; otherwise the blank can effectively contribute zero while its weight remains in the denominator. Review the data and choose ranges or a data-cleaning approach that matches the intended meaning.

Does the formula work with whole-number weights?
Yes. For example, weights of 20, 30, and 50 give the same average as 20%, 30%, and 50%, because the formula divides by their total. Do not mix whole-number weights with percentage values in the same calculation.

Can I calculate a weighted average across a row instead of a column?
Yes. Arrange each value beside its corresponding weight in two aligned horizontal ranges, then use SUMPRODUCT on the value row divided by SUM of the weight row. Keep the ranges the same width and in matching order.

Why is my result outside the range of the values?
With nonnegative weights and a positive total, a weighted average should fall between the lowest and highest included values. If it does not, check for negative weights, incorrect value-weight pairings, or a formula that includes different ranges in its numerator and denominator.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.