Excel SUMPRODUCT & FILTER Advanced: OR/Complex Condition Aggregation & Dynamic Ranges
Use the Excel SUMPRODUCT FILTER combination to easily handle OR and complex conditions that are difficult with SUMIFS. Includes reproducible tables and formulas, plus how to link spill (#) ranges.
Quick Fix
=SUMPRODUCT( (Sales[Channel]="Online") + (Sales[Channel]="Store"), Sales[Revenue] )=SUM( FILTER(Sales[Revenue], (Sales[Category]="Shoes")*(Sales[Date]>=TODAY()-7) + (Sales[Category]="Outer") ) )Key Concepts
- AND = multiplication (*), OR = addition (+)
- Use FILTER to extract only rows that meet the conditions, then apply SUM/COUNT
- Use LET to improve readability and performance
Practical Examples
OR Aggregation
=SUMPRODUCT( (Sales[Channel]="Online") + (Sales[Channel]="Store"), Sales[Revenue] )Complex Aggregation
=SUMPRODUCT( ((Sales[Category]="Shoes")*(Sales[Date]>=TODAY()-7)) + (Sales[Category]="Outer"), Sales[Revenue] )Conditional Average/Weighted Average
=LET(r, FILTER(Sales[Revenue], Sales[Category]="Shoes"), IFERROR(AVERAGE(r),0))Category Totals (Spill)
=LET(cats, UNIQUE(Sales[Category]), sums, MAP(cats, LAMBDA(c, SUM(FILTER(Sales[Revenue], Sales[Category]=c)))), HSTACK(cats, sums))Checklist
- Use + for OR and * for AND
- Use parentheses to clarify precedence
- Check data types (numbers/dates)
- Filter the data first with FILTER
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| #VALUE! | Mixed data types | Format the data or convert it with VALUE/– |
| Total is too high | Overlapping OR conditions | Consolidate the OR block with >0 |
| #SPILL! | Range conflict | Clear the spill range |
Conclusion
The SUMPRODUCT and FILTER combination standardizes aggregation with OR and complex conditions. The next article covers automatically generating monthly reports.