What it does
SUMIFS totals values that match multiple conditions.
Syntax or pattern
=SUMIFS(sum_range, criteria_range1, criteria1, ...)✅
5 practical examples
1
Total by region and product
Sum sales for East region and Coffee product.
=SUMIFS(Sales[Amount],Sales[Region],"East",Sales[Product],"Coffee")Each criteria pair narrows the total.
2
Total inside a date range
Sum values between start and end dates.
=SUMIFS(Sales[Amount],Sales[Date],">="&F1,Sales[Date],"<="&G1)Concatenate comparison operators with date cells.
3
Total by status
Sum open invoice amounts.
=SUMIFS(Invoices[Amount],Invoices[Status],"Open")A single-condition SUMIFS is also valid.
4
Total excluding a value
Sum amounts where status is not cancelled.
=SUMIFS(Orders[Amount],Orders[Status],"<>Cancelled")Use <> for not equal.
5
Total by dynamic inputs
Use dropdown cells as criteria.
=SUMIFS(Sales[Amount],Sales[Region],H2,Sales[Month],H3)Great for dashboard summaries.
Common mistakes to avoid
- Selecting ranges with different sizes.
- Using text values where numbers are required.
- Forgetting how blanks and hidden rows are treated.
Related Excel examples
FAQ
Can this formula use Excel Tables?
Yes. Structured references often make summary formulas easier to read.