Array Formulasintermediate
Filter rows by a condition with FILTER
=FILTER(A2:C100,B2:B100="East","No results")
Excel how-to guide
How to sumproduct weighted average in Excel
Calculate a grade weighted by credit hours, a sales average weighted by volume, or a multi-condition count that COUNTIFS cannot handle alone.
Step-by-step instructions
How to sumproduct weighted average — 3 steps
1
Enter the values array as the first argument and the weights array as the second — both must be the same size.
2
SUMPRODUCT multiplies each pair of values and sums all the products.
3
Divide by SUM of the weights to convert from a weighted total to a weighted average.
Example data
Worked example
| Grade | Credit Hours |
|---|---|
| 90 | 3 |
| 85 | 4 |
| 78 | 2 |
| 92 | 3 |
Common mistakes
Errors to watch out for
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.
FAQ
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.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Math & Statisticsintermediate
SUMIFS for monthly revenue by category
=SUMIFS($D:$D,$B:$B,$G$2,$C:$C,">="&DATE(2026,1,1),$C:$C,"<"&DATE(2026,2,1))