Array Formulasintermediate
Calculate a weighted average with SUMPRODUCT
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
Function reference
SUMPRODUCT in Excel & Google Sheets
SUMPRODUCT is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.
1 intermediateBuild a custom SUMPRODUCT formula →
When to use SUMPRODUCT
- Calculate a grade weighted by credit hours, a sales average weighted by volume, or a multi-condition count that COUNTIFS cannot handle alone.
Worked examples (1)
Common errors with SUMPRODUCT
Arrays are different sizes
Both arrays in SUMPRODUCT must have the same number of rows and columns. If they differ, SUMPRODUCT returns a #VALUE error.
Dividing by zero
If the weights column is all zeros or empty, SUM(C2:C10) returns 0 and the formula errors. Wrap the denominator in an IF to guard against this case.
Frequently asked questions
How do I use SUMPRODUCT as a conditional count?
Use boolean arrays — =SUMPRODUCT((A2:A100="East")*(B2:B100>1000)) counts rows where region is East AND amount exceeds 1000, with no Ctrl+Shift+Enter needed.
Need a custom variation?
Describe your specific SUMPRODUCT problem and get a formula written for your exact data.
Open generator with SUMPRODUCT