Excel SUMIFS and AVERAGEIFS Practical Patterns: Multi-Criteria Aggregation Made Easy
Use Excel SUMIFS and AVERAGEIFS to calculate totals and averages accurately and quickly when you have multiple criteria such as date, region, and category. This guide covers standard templates and common pitfalls, including numbers stored as text, spaces, and date-range handling.
Quick Fix (3 Minutes)
- Convert the source data to an Excel Table (Ctrl+T) (SalesT).
- Basic total:
=SUMIFS(SalesT[Amount], SalesT[Region], "East") - Date range:
=SUMIFS(SalesT[Amount], SalesT[Date], ">="&G2, SalesT[Date], "<="&H2) - Partial match:
=SUMIFS(SalesT[Amount], SalesT[Category], "*JEANS*") - Average:
=AVERAGEIFS(SalesT[Amount], SalesT[Region], "East", SalesT[Category], "TOP")
Key Concepts
- Combine comparison operators as text:
">="&cell. - Wildcards:
*for zero or more characters,?for one character, and~as an escape character. - Each criterion is combined with AND logic; use multiple formulas added together for OR logic.
Practical Example
| Date | Region | Category | Amount |
|---|---|---|---|
| 2025-01-05 | East | TOP | 120000 |
| 2025-01-20 | West | BTM | 90000 |
| 2025-02-03 | East | TOP | 80000 |
| 2025-02-14 | South | ACC | 45000 |
| 2025-02-25 | East | JEANS | 130000 |
| 2025-03-09 | North | BTM | 150000 |
| 2025-03-20 | East | JEANS-SLIM | 110000 |
Date Range Total
=SUMIFS(SalesT[Amount], SalesT[Date], ">="&$G$2, SalesT[Date], "<="&$H$2)Monthly Total
=SUMIFS(SalesT[Amount],
SalesT[Date], ">="&EOMONTH($A$2,-1)+1,
SalesT[Date], "<="&EOMONTH($A$2,0))Partial Match
=SUMIFS(SalesT[Amount], SalesT[Category], "*JEANS*")OR Criteria
=SUMIFS(SalesT[Amount], SalesT[Region], "East")
+SUMIFS(SalesT[Amount], SalesT[Region], "West")Average and Share
=AVERAGEIFS(SalesT[Amount], SalesT[Region], "East", SalesT[Category], "*JEANS*")
=SUMIFS(SalesT[Amount], SalesT[Region], "East")/SUM(SalesT[Amount])Alternatives and Enhancements
- Excel Tables and structured references make formulas easier to maintain.
- Defined names improve the readability of date-range and criteria cells.
- Use FILTER for preprocessing, then combine it with
SUM. - PivotTables are useful for complex axes and filters.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Result is 0 | Dates or numbers stored as text | Use VALUE, Text to Columns, or consistent formatting |
| Only some records are included | Mismatched ranges | Use table references |
| Wildcard does not work | Missing symbol | Check the rules for * / ? / ~ |
| Date-range error | Operator concatenation is missing | Use the ">="&cell format |
| Space issue | CHAR(160) | Use SUBSTITUTE, then TRIM/CLEAN |
| Slow performance | Full-column references | Minimize column ranges or use a PivotTable |
Conclusion & Internal Links
- MAXIFS and MINIFS: Conditional Maximum and Minimum Values
- Get Started with Excel PivotTables in 10 Minutes
- Preprocess Data with TEXTSPLIT
- VLOOKUP with Multiple Criteria
- IF Function Branching Patterns