SUMIFS function
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.
- Available in
- Every current version
- Returns
- The total of the values that meet all the conditions, or 0 if none do.
- Google Sheets
- Also available in Google Sheets.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Date | Region | Product | Amount | Check | Result | |
| 1/14/2026 | East | Widget | $1,200 | East widgets | $2,150 | |
| 2/3/2026 | West | Gadget | $800 | February total | $2,850 | |
| 2/17/2026 | East | Widget | $650 | East, over $1,000 | $2,600 | |
| 2/25/2026 | East | Gizmo | $1,400 | |||
| 3/2/2026 | West | Widget | $900 | |||
| 3/9/2026 | East | Widget | $300 |
Practice SUMIFS
From lesson 8, COUNTIFS, SUMIFS & AVERAGEIFS. Graded instantly, then re-checked on changed data.
Two-condition total
G5, total the revenue for the region in G2 and the product in G3. Reference the input cells.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.
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"})).
Lessons that use SUMIFS
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.