Resources > Formula Help
How Do I Use SUMIFS in Excel?
Last Updated: June 2026
Quick Answer
SUMIFS adds numbers that meet one or more criteria. It is one of the most useful functions for reporting, dashboards, KPI tracking, budgeting, and business analysis.
Basic syntax:
=SUMIFS(sum_range, criteria_range1, criteria1)
Simple SUMIFS Example
Suppose you have the following data:
| Region | Sales |
|---|---|
| East | 500 |
| West | 300 |
| East | 700 |
To calculate total sales for the East region:
=SUMIFS(B:B,A:A,"East")
Result:
1200
SUMIFS with Multiple Conditions
Now imagine you have:
| Region | Status | Sales |
|---|---|---|
| East | Complete | 500 |
| East | Pending | 300 |
| East | Complete | 700 |
To total only completed sales in the East region:
=SUMIFS(C:C,A:A,"East",B:B,"Complete")
Result:
1200
Using Cell References
Instead of hard-coding values, reference cells:
=SUMIFS(C:C,A:A,E2,B:B,F2)
Where:
- E2 contains the region
- F2 contains the status
This makes dashboards and reports dynamic.
SUMIFS with Dates
You can sum values within a date range.
=SUMIFS( C:C, A:A,">="&DATE(2026,1,1), A:A,"<="&DATE(2026,12,31) )
This returns all sales occurring during 2026.
Common SUMIFS Mistakes
1. Sum Range Different Size
All ranges must contain the same number of rows.
Bad:
=SUMIFS(C1:C100,A1:A50,"East")
Good:
=SUMIFS(C1:C100,A1:A100,"East")
2. Numbers Stored as Text
If your data looks like numbers but is stored as text, SUMIFS may return incorrect results.
3. Hidden Spaces
Extra spaces can prevent criteria from matching.
=TRIM(A2)
Use TRIM to clean imported data.
SUMIFS vs SUMIF
| Function | Purpose |
|---|---|
| SUMIF | One condition |
| SUMIFS | Multiple conditions |
Even if you only have one condition today, many Excel users prefer SUMIFS because it scales more easily as reporting requirements grow.
Real-World Uses
- Sales reporting
- Project tracking
- Department budgets
- KPI dashboards
- Expense analysis
- Customer reporting
Need Help Building Excel Reports?
SUMIFS is one of the most powerful reporting functions in Excel, but it can become difficult to troubleshoot in large workbooks.
If you need help building dashboards, reports, or automated spreadsheets, I can help.
