A weighted average needs both weighted contributions and the total weight. An ordinary average of the values gives a different result when their weights differ.
Pair values with their weights
Place values in A2:A3 and corresponding weights in B2:B3. With values 80 and 100 and weights 1 and 3, =SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3) gives 95.
SUMPRODUCT multiplies corresponding entries and adds those products. Its paired arrays must have matching dimensions and refer to the same records. Sorting one column independently destroys that pairing.
Define missing and invalid cases
Confirm all included values and weights are numeric and use the intended units. A missing score should not automatically contribute zero while its full weight remains in the denominator.
Decide whether negative weights are meaningful for your model. If the total included weight is zero, the weighted average is undefined; show that state explicitly rather than reporting a fabricated zero average.
Reconcile numerator and denominator
For the example, the numerator is 380 and the denominator is 4. Check those pieces separately before applying the formula to a larger dataset. Test one excluded or missing record according to the policy you chose.
Do not average subgroup averages unless their corresponding weights are carried forward correctly. Label the result’s units and document whether the weights are counts, proportions, or another measure. If you filter the visible sheet, verify whether the formula should still include hidden records; a normal SUMPRODUCT range is not automatically restricted to only the rows currently displayed in a filtered view.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.