Formula example
SUMIFS Multiple Criteria Formula Example
Total sales only when Region is East and Product is Widget. With the sample data, the matching rows return 1060.
Copyable formula
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")Returns 1060 from the East Widget rows: 420 plus 640.
Useful variations
=SUMIFS(D2:D100, B2:B100, F2, C2:C100, G2)Use F2 for Region and G2 for Product so the report can be reused without editing the formula.
=SUMIFS(D2:D100, B2:B100, "East", A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)F1 contains any real date in the month you want to total.
=SUMIFS(D2:D100, B2:B100, "East", D2:D100, ">300")Totals East rows where Amount is greater than 300.
Sample data
| Date | Region | Product | Amount |
|---|---|---|---|
| 2026-01-04 | East | Widget | 420 |
| 2026-01-12 | West | Widget | 310 |
| 2026-02-03 | East | Gadget | 275 |
| 2026-02-15 | East | Widget | 640 |
When to use this formula
- Use SUMIFS when the result should be a total, not a count or a returned row.
- Add one criteria range and one criteria value for each condition that must be true on the same row.
- Use this pattern for region plus product, month plus region, owner plus status, and similar report totals.
Add criteria pairs
SUMIFS reads each condition as a range and criteria pair. Every pair must match on the same row before the value in the sum range is included.
The sum range comes first, then each criteria range and criteria value follows in pairs.
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget", A2:A100, ">=2026-02-01")Adds a start date to the region and product criteria.
Excel and Google Sheets support
The same SUMIFS syntax works in Excel and Google Sheets for text, number, and date criteria.
For dates, compare real date values rather than formatted month names. A before-next-month boundary avoids missing rows that include time values.
Troubleshoot zero totals
A zero result usually means one criteria pair is not matching, the ranges are different sizes, or the source values are stored as text.
Test each condition by temporarily removing the other criteria pairs, then add them back one at a time.
Returned totals from the sample data
| Criteria | Returned result | Rows included |
|---|---|---|
| Region = East and Product = Widget | 1060 | 2026-01-04 East Widget 420 plus 2026-02-15 East Widget 640. |
| Region = West and Product = Widget | 310 | Only the 2026-01-12 West Widget row. |
| Region = East and Product = Gadget | 275 | Only the 2026-02-03 East Gadget row. |
Criteria pair examples
| Need | Criteria pair | Example |
|---|---|---|
| Exact text | Region range plus quoted text | B2:B100, "East" |
| Reusable text criteria | Region range plus a cell reference | B2:B100, F2 |
| Greater than a number | Amount range plus quoted operator | D2:D100, ">300" |
| Selected month | Date range with first day and before next month | A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1) |
Formula explanation
- D2:D100 is the range being summed.
- B2:B100 must equal East on the same row.
- C2:C100 must equal Widget on the same row.
- SUMIFS uses AND logic, so both criteria must be true before the amount is included.
Common errors
- Criteria ranges must match the sum range shape.
- Text criteria require quotes.
- SUMIFS uses AND logic across criteria pairs.
- Date criteria should compare real dates, not month names typed as text.
- Extra spaces in source text can make a valid-looking criterion return zero.
Build your own version
Use the formula builder for this pattern: SUMIFS Formula Builder.
Related formulas
FAQ
Can SUMIFS use OR logic?
Not directly in one criteria pair. Use multiple SUMIFS formulas or array logic for OR cases.
Does criteria order matter?
No, but keeping ranges near the source column order makes formulas easier to audit.
How many criteria can SUMIFS use?
Excel and Google Sheets can handle many criteria pairs, but most report formulas stay easier to audit when each pair has a clear business meaning.
Why does my SUMIFS multiple criteria formula return zero?
Check that every criteria range has the same size as the sum range, text values do not contain extra spaces, and date criteria compare real date values.
Does the same formula work in Google Sheets?
Yes. Google Sheets uses the same SUMIFS argument order for text, number, and date criteria.