Conditional formula page
SUMIFS Formula for Multiple Criteria
Use SUMIFS when every matching row must satisfy two or more criteria before its amount is added. This example totals only East Widget sales and shows how to extend the pattern.
Adding values by multiple text, number, or date criteria with SUMIFS.
Returns 1060 from the East Widget rows in the sample data: 420 plus 640.
Copy formulas
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")Excel and Google Sheets use the same SUMIFS argument order: the sum range comes first, followed by aligned criteria-range and criterion pairs.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
Returns 1060 from the East Widget rows in the sample data: 420 plus 640.
How the formula works
- D2:D100 is the sum range, so only values from the Amount column are added.
- B2:B100 must equal East on the same row as the amount.
- C2:C100 must equal Widget on the same row as the amount.
- SUMIFS uses AND logic, so all criteria pairs must match before a row is included.
| Syntax piece | Role in the formula |
|---|---|
| D2:D100 | The Amount column that is added after every criterion matches on the same row. |
| B2:B100, East | The first criteria pair keeps rows whose Region is East. |
| C2:C100, Widget | The second criteria pair keeps East rows whose Product is Widget. |
Verified examples
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")Excel: Use the sample sales table, where East Widget appears with amounts 420 and 640. Returns: 1060
=SUMIFS(D2:D100, B2:B100, F2, C2:C100, G2)Google Sheets: Enter East in F2 and Widget in G2. Returns: 1060
=SUMIFS(D2:D100, B2:B100, "East", A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1))Excel: Use an inclusive January start and an exclusive February start boundary. Returns: 420
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| SUMIFS returns 0 even though matching rows are visible | A criterion differs from the source text, a date is stored as text, or amounts are not numeric values. | Test each criterion separately and confirm the Amount cells are numbers before combining the conditions. |
| SUMIFS returns #VALUE! | The sum range and one or more criteria ranges contain different row counts. | Use aligned ranges such as D2:D100, B2:B100, and C2:C100. |
| A copied formula changes the criteria unexpectedly | Criteria cells or source ranges were left relative when the formula was filled across a report. | Lock reusable source ranges with dollar signs and choose relative or absolute criteria cells deliberately. |
When not to use this formula
- Use COUNTIFS for a row count and FILTER or QUERY when the matching records themselves must be returned.
Alternatives
| Alternative | When to use it |
|---|---|
| SUMIFS Formula Builder | Use to assemble aligned ranges and criteria interactively before copying the formula. |
| COUNTIFS Formula | Use when the required result is the number of matching rows rather than the Amount total. |
Related formulas
Official references
- SUMIFS function from Microsoft
FAQ
Does SUMIFS combine its conditions with AND or OR?
SUMIFS uses AND logic across criteria pairs, so Region and Product must both match on the same row before Amount is added.
Why must every SUMIFS range be the same size?
SUMIFS evaluates aligned rows. A criterion in row 12 must be paired with the amount from row 12, so all ranges need matching boundaries.