SUMIFS function
Quick answer
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)
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
Answers that use SUMIFS
In other languages
SUMIFS is SUMAR.SI.CONJUNTO in Spanish Excel and SUMMEWENNS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .