Excel SUMPRODUCT Function Guide: Weighted Sums, Multiple Criteria, and Conditional Counts
The Excel SUMPRODUCT function uses the simple principle of “multiplying arrays and then adding the results” to handle multiple-criteria totals, weighted averages, and conditional counts in one formula. This guide covers everything from a Quick Fix that beginners can follow right away to practical tables, formulas, and reproducible examples with expected results.
Quick Fix — Create a Multiple-Criteria Total in 3 Minutes
- Select the cell where you want to display the result.
- Connect the criteria with multiplication, multiply by the target column to be summed, and wrap the formula in
SUMPRODUCT. - Press Enter.
=SUMPRODUCT( --(A2:A100="Sales"), --(B2:B100="2025-09"), C2:C100 )Quick reference: If you frequently total values with multiple criteria, see the Complete SUMIFS Function Guide. For looking up unit prices by criteria, see the Complete XLOOKUP Guide.
SUMPRODUCT Basics and Syntax
=SUMPRODUCT(array1, [array2], [array3], ...)- It multiplies corresponding elements and then adds all the results.
- All arrays must be the same size.
How Array Multiplication and Addition Work
=SUMPRODUCT(C2:C5, D2:D5)This calculates quantity×unit price for each row and adds the results.
Converting TRUE/FALSE to Numbers with “–” (Double Unary)
A2:A100="Sales" creates a TRUE/FALSE array. Adding -- in front forces each value to convert to 1/0. Multiplying these 1/0 values by the target column makes them work like a filter.
Practical Example 1 — Multiple-Criteria Total (Department = Sales, Month = 2025-09)
| Row | Department | Month (yyyy-mm) | Sales |
|---|---|---|---|
| 2 | Sales | 2025-09 | 120,000 |
| 3 | Sales | 2025-08 | 95,000 |
| 4 | Development | 2025-09 | 67,000 |
| 5 | Sales | 2025-09 | 88,000 |
| 6 | Sales | 2025-10 | 134,000 |
Goal: Total sales for rows where Department = Sales ∧ Month = 2025-09
=SUMPRODUCT( --(A2:A6="Sales"), --(B2:B6="2025-09"), C2:C6 )Expected result: 208,000
If the month is an actual date, use date range comparisons (>= first day and < first day of the next month) instead of "2025-09". For COUNT functions, see COUNTIFS for Multiple-Criteria Counts.
Practical Example 2 — Weighted Average
| Item | Score | Weight |
|---|---|---|
| Assignment | 85 | 0.2 |
| Midterm | 92 | 0.3 |
| Final Exam | 88 | 0.5 |
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)Practical Example 3 — Conditional Count (Contains Text/Case-Insensitive)
Goal: Count rows containing the word “Urgent”
=SUMPRODUCT( --ISNUMBER( SEARCH("Urgent", D2:D100) ) )Alternative Methods and Comparison — SUMIFS, PivotTables, and Power Pivot
- SUMIFS: Best for simple conditional totals, with excellent readability and speed
- SUMPRODUCT: Highly flexible for complex criteria, weighted sums, conditional averages, counts, and more
- PivotTables/Power Pivot: Best for large-scale or relational analysis
Related: IF Function Basics and Applications, Complete XLOOKUP Guide
Performance Optimization and Precautions
- Minimize ranges: Use only the actual data range instead of entire columns such as A:A.
- Same-size rule: Make all arrays the same size (
#VALUE!occurs when they do not match). - Mixed text and blank cells: Use
--to clearly convert values to 1/0. - Large datasets: Consider moving to a PivotTable or data model.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| #VALUE! | Array sizes do not match | Make all ranges the same size |
| Returns 0 | All criteria are FALSE | Check quotation marks, spaces, and formats |
| Slow | Entire-column references or repeated SEARCH | Reduce ranges or consider helper columns or a PivotTable |
| Missing results | Incorrect date comparison method | Use a range (>= first day, < first day of the next month) |
| Overcounted total | Text numbers or blank cells are mixed in | Normalize with VALUE/-- |
Conclusion & Recommended Next Articles
You can now use the Excel SUMPRODUCT function to handle multiple-criteria totals, weighted averages, and conditional counts in one formula. Next, review the Complete SUMIFS Function Guide, Complete XLOOKUP Guide, and COUNTIFS for Multiple-Criteria Counts.