# SUMIFS function in Excel

> SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.

Source: https://spreadsheetpractice.com/functions/sumifs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.
- **Syntax:** `=SUMIFS(sum_range, criteria_range1, criteria1, ...)`
- **Most common use:** `=SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Widget")` returns $2,150 (Two text conditions on different columns)
- **Available in:** Every current version
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The total of the values that meet all the conditions, or 0 if none do.
- **Google Sheets:** Also available in Google Sheets.

## What it does

SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer. The sum range comes first, followed by as many range-and-criterion pairs as you need. It's the workhorse behind most summary reports.

## Syntax

`=SUMIFS(sum_range, criteria_range1, criteria1, ...)`

| Argument | Required? | What it is |
|---|---|---|
| `sum_range` | Required | The cells to add. |
| `criteria_range1` | Required | The first range to check. |
| `criteria1` | Required | The condition for the first range. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | `=SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Widget")` | $2,150 | Two text conditions on different columns |
| G3 | `=SUMIFS(D2:D7,A2:A7,">="&DATE(2026,2,1),A2:A7,"<"&DATE(2026,3,1))` | $2,850 | A date range: on or after Feb 1 and before Mar 1 |
| G4 | `=SUMIFS(D2:D7,B2:B7,"East",D2:D7,">1000")` | $2,600 | The sum range can also be a criteria range |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Product | Amount |  | Check | Result |
| 2 | 1/14/2026 | East | Widget | $1,200 |  | East widgets | $2,150 |
| 3 | 2/3/2026 | West | Gadget | $800 |  | February total | $2,850 |
| 4 | 2/17/2026 | East | Widget | $650 |  | East, over $1,000 | $2,600 |
| 5 | 2/25/2026 | East | Gizmo | $1,400 |  |  |  |
| 6 | 3/2/2026 | West | Widget | $900 |  |  |  |
| 7 | 3/9/2026 | East | Widget | $300 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The sum range and criteria ranges are different sizes. Make them all cover the same rows. |
| The total is 0 for a date range | Dates in criteria must be joined as numbers: ">="&DATE(2026,2,1) or ">="&F2, not ">=F2". |
| Arguments in SUMIF order | SUMIFS starts with the sum range. Writing it last (SUMIF style) gives wrong totals or errors. |

## Tips

- Build a summary grid with criteria in the row and column headers, and use mixed references like $B$2:$B$7,$F3 to fill it in one go.
- SUMIFS returns 0 rather than an error when nothing matches, so a total of 0 is worth a second look at the criteria.

## Related functions

- [SUMIF](https://spreadsheetpractice.com/functions/sumif): SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500.
- [COUNTIFS](https://spreadsheetpractice.com/functions/countifs): COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.
- [AVERAGEIFS](https://spreadsheetpractice.com/functions/averageifs): AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.
- [SUMPRODUCT](https://spreadsheetpractice.com/functions/sumproduct): SUMPRODUCT multiplies matching items in two or more ranges and adds the results.
- Comparison: [SUMIF vs SUMIFS](https://spreadsheetpractice.com/functions/compare/sumif-vs-sumifs)

## Questions

### How do I sum with multiple criteria?

Use SUMIFS: =SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Widget").

### How do I sum between two dates?

Use two conditions on the date column: =SUMIFS(D2:D7,A2:A7,">="&start,A2:A7,"<="&end).

### Can SUMIFS use OR logic?

Not in one call. Add two SUMIFS together, or use SUM(SUMIFS(D2:D7,B2:B7,{"East","West"})).

## Practice

A graded SUMIFS exercise ("Two-condition total") is on [the SUMIFS page](https://spreadsheetpractice.com/functions/sumifs). It is checked instantly, then re-checked on changed data.

Lessons that use SUMIFS:

- [Lesson 8: COUNTIFS, SUMIFS & AVERAGEIFS](https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs)
- [Lesson 32: Self-building summary tables](https://spreadsheetpractice.com/learn/summary-tables-unique-sumifs) (Pro)
- [Lesson 44: Capstone: sales report](https://spreadsheetpractice.com/learn/capstone-sales-report) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
