Excel Weighted Average Using SUMPRODUCT: Formula and Examples

Formulas & functions · Updated November 19, 2026

To calculate a weighted average in Excel, multiply each value by its weight, add those products, then divide by the sum of the weights. With values in B2:B6 and their weights in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6). SUMPRODUCT handles the pairwise multiplication and addition, so the formula works without a helper column.

How the SUMPRODUCT weighted average works

A regular average gives every value the same influence. A weighted average gives some values more influence by assigning them larger weights. The calculation has two parts: the numerator is the sum of each value multiplied by its weight, and the denominator is the total weight. Dividing the first by the second scales the result back to the same units as the original values.

SUMPRODUCT multiplies items in corresponding positions in its ranges, then adds the products. In the formula above, B2 pairs with C2, B3 with C3, and so on. Excel then divides that combined total by the sum of C2:C6. The value range and weight range must cover the same number of rows or columns, and each value must be paired with the weight that belongs to it.

Weights do not have to be percentages. They can be counts, proportions, hours, units sold, or any other figures that represent relative importance. If the weights are proportions that sum to 1, or percentages that sum to 100%, the denominator makes the formula work either way. Multiplying all weights by the same positive number does not change the answer.

Worked example with scores and weights

Suppose a course grade combines five assessments. The scores are in B2:B6 and the percentage weights are in C2:C6. Enter the following data:

Assessment Score Weight
Quiz 1 82 20%
Quiz 2 90 30%
Project 76 10%
Midterm 88 25%
Final 95 15%

With scores in B2:B6 and weights in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6). The weighted products total 8,725, and the weights total 100, so the result is 87.25. The result is a score, not a percentage formatted by the formula; apply the number format you want to the result cell.

In this example, the weights already add to 100%, but dividing by their sum is still useful. It protects the calculation if the weights are entered as whole numbers such as 20, 30, 10, 25, and 15. It also normalizes weights that do not total exactly 100 because of rounding or that are intentionally entered on a different scale.

Choose weights that match the question

The formula is straightforward; deciding what the weights mean is often the harder part. A weight should reflect how much influence each value is meant to have in the result. For example, if a product’s average selling price should account for units sold, use the number of units as the weight. If each month should count equally, use equal weights or an ordinary average instead.

  • Course grades: Use each assessment’s share of the final grade. Confirm whether the weights are percentages or points and whether all required assessments are included.
  • Average price: Use quantities purchased or sold as weights when calculating the average price per unit across transactions.
  • Survey or category summaries: Use group sizes when combining group averages, provided each group average represents the same kind of measure.
  • Time-based results: Use the number of hours, days, or observations represented by each value when periods differ in length.

Be careful when combining averages. If each row is already an average based on a different number of observations, the observation count is often the appropriate weight for an overall mean. Weighting those averages equally answers a different question: the average of the group averages, where every group has equal influence regardless of size.

Use SUMPRODUCT for selected rows

You can include only rows that meet a condition by adding a condition array to SUMPRODUCT. For example, suppose item values are in B2:B6, weights are in C2:C6, and A2:A6 contains a status. To calculate the weighted average for rows marked Include, use the following formula.

With statuses in A2:A6, values in B2:B6, and weights in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6,--(A2:A6="Include"))/SUMPRODUCT(C2:C6,--(A2:A6="Include")). The double unary operator converts the TRUE/FALSE results of the condition into 1s and 0s. Rows marked Include contribute to both the weighted total and the weight total; other rows contribute zero.

The same condition appears in both parts for a reason. Filtering only the numerator would leave excluded rows’ weights in the denominator and produce the wrong result. If no row matches, the denominator is zero, so decide how the workbook should handle that case rather than assuming a matching record always exists.

Handle blanks and zero total weight

A blank value paired with a positive weight can be misleading. SUMPRODUCT treats a blank numeric position as contributing zero to the product, while SUM still includes the numeric weight. The blank row can therefore pull the weighted average downward. If a blank means “not yet supplied” rather than a real zero, exclude that row from both the numerator and denominator.

With values in B2:B6 and weights in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6,--(B2:B6<>""))/SUMPRODUCT(C2:C6,--(B2:B6<>"")) to ignore rows where the value cell is blank. This approach assumes that a blank value should be omitted and that the corresponding weight is numeric. A genuine score or measurement of zero is not blank, so it remains in the calculation.

If the relevant weights total zero, Excel returns a division-by-zero error. You can show a blank result instead, but first make sure a zero total weight is genuinely an expected condition. With values in B2:B6 and weights in C2:C6, use =IFERROR(SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6),"") to display an empty string if the calculation returns an error. IFERROR can also conceal unrelated errors, so it is better to check the source data while building or auditing a workbook.

Common SUMPRODUCT mistakes to check

If the result looks unexpected, inspect the pairings and the denominator before changing the formula. A correct calculation can still answer the wrong question if the weights are attached to the wrong records or include rows that should have been excluded.

  • Ranges have different sizes: The value and weight ranges must have matching dimensions. If values run through row 20 but weights stop at row 19, correct the ranges so every record has a partner.
  • Rows are out of alignment: Sorting one column without the others can pair a value with someone else’s weight. Sort the whole table, or use a structured table so the fields stay together.
  • Weights are stored as text: Numbers imported as text may not behave like numeric weights. Check the cell format and convert the source data to numbers when needed; changing the display format alone does not necessarily convert text.
  • Weights are negative or zero: Zero-weight rows have no influence, but negative weights are not appropriate for most ordinary averages. Use them only if the calculation’s definition explicitly calls for them.
  • Weights total zero: The formula cannot divide by zero. Check whether weights are missing, incorrectly entered, or cancelled by negative values.
  • Percentages are entered inconsistently: Excel stores 20% as 0.2. Mixing 20 with 0.3 may not change a normalized result in every case, but it makes the data hard to interpret and can cause errors when combined with other calculations.

When a formula produces an error, test the ranges and data types first. When it produces a plausible but incorrect number, verify the weight definition, row alignment, and which rows belong in the calculation. Formatting a result as a percentage or rounding its display changes how it looks, not the underlying weighted-average calculation.

SUMPRODUCT versus other Excel approaches

For a small calculation, a helper column can make the logic easier to audit. With values in B2:B6 and weights in C2:C6, a third column can contain each value-times-weight product; then sum that column and divide by the weight total. This uses more cells, but makes individual contributions visible and can help readers investigate an unusual result.

SUMPRODUCT is more compact and avoids maintaining a separate product column. It is especially useful when the weighted average is part of a summary calculation or when conditions determine which records count. However, a long formula with multiple conditions can be harder to review than a clearly labeled helper column. Choose the layout that makes the workbook easiest for its users to inspect and maintain.

Do not substitute AVERAGE when the observations have different weights: AVERAGE treats each numeric entry equally. It is appropriate when each value should have equal influence. Also distinguish a weighted average from a median or a rate calculated from totals; those answer different questions. For example, an overall rate may need total successes divided by total attempts rather than an average of several group rates, depending on what the rate represents.

Frequently Asked Questions

Do the weights have to add up to 100%?
No. The formula divides by the sum of the weights, so weights such as 2, 3, and 5 work just as well as 20%, 30%, and 50%. They must represent the intended relative importance, and their sum must not be zero.

Can I use SUMPRODUCT if my weights are percentages?
Yes. Excel stores percentage entries as decimal values, and the division by the sum of the weights normalizes them. If the percentages total 100%, the denominator is 1; if they total 100 as whole numbers, the denominator is 100.

Why does my weighted average show #DIV/0!?
The denominator is zero, usually because the weight range is empty, its values sum to zero, or a condition excluded every row. Check the included weights and confirm that at least one valid row contributes a nonzero total weight.

Should blank values count as zero?
Only if zero is what a missing value means for the question being answered. If a blank means data is unavailable, exclude the row from both the weighted sum and the sum of weights; otherwise it is effectively treated as a zero-valued observation with its weight still counted.

Does SUMPRODUCT work in Excel for Mac?
Yes. SUMPRODUCT is available in Excel for Microsoft 365 on Windows and Mac, and the calculation is the same. Depending on regional settings, Excel may expect semicolons rather than commas between formula arguments; use the separator accepted by your installation.

Make the result easier to maintain

For a workbook that will grow, keep values and weights in clearly labeled columns and consider converting the source range to an Excel table with Insert > Table. Tables expand when rows are added and make it easier to keep records together during sorting and filtering. Check that any formula ranges include new records, especially if the data is not stored in a table.

Finally, document what the weights mean and how missing entries are treated. A short note such as “weight = units sold; blank values excluded” can prevent someone from reusing the formula for a different purpose. The SUMPRODUCT calculation is only as meaningful as the definitions, pairings, and data behind it.

Related

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.