SUMIFS is the modern answer to conditional totals. Where SUMIF handles one condition, SUMIFS handles as many as you need — region and month, customer and amount, category and date range — all in a single formula that reads top to bottom.
This guide covers the syntax (including the argument order that trips everyone up), every operator, wildcards, five real-world examples, the SUMIF vs SUMIFS decision, date-based totals, the OR-logic workaround, cross-sheet summing, a dashboard walkthrough, and the errors that quietly return the wrong number.
SUMIFS adds the values in a range that meet multiple conditions, written as =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).
What Is the SUMIFS Function?
SUMIFS is Excel’s multi-condition sum. It walks through every criteria range, tests each one against its paired condition, and adds the values from sum_range only where all conditions are TRUE.
The key detail — and the reason so many people get it wrong at first — is that the sum range comes first. This is the opposite of SUMIF, and it is the single most common source of confusion between the two functions.
How SUMIFS evaluates a formula
Take the formula =SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">1000"). Excel does three things, in order:
- Test the first criteria pair. It reads every cell in
A2:A100and asks: is this cell “East”? - Test every additional pair. It reads every cell in
B2:B100and asks: is this cell greater than 1000? A row must pass every test to survive. - Add the sum range. It adds the values in
C2:C100for the surviving rows only. Rows that fail any test are ignored.
Every row must satisfy every condition to be included. If you need “East OR West”, you cannot write it in a single criteria pair — see the section on OR logic later in this guide.
It handles one condition just as well as many, and it is the modern function. If you are unsure which to reach for, SUMIFS is the safer default.
Syntax and Arguments
The full signature takes one sum range followed by any number of criteria pairs. Only the first pair is required; each additional pair is optional.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Walking through each argument
- sum_range — the cells you want to add. Comes first, before any criteria. This is the opposite of SUMIF.
- criteria_range1 — the cells you want to test against the first condition. Must be the same size as
sum_range. - criteria1 — the condition to match. Text and operators must be in double quotes.
- criteria_range2, criteria2 … (optional) — additional pairs, up to 127 total. Every pair is combined with AND.
| Argument | Required? | Notes |
|---|---|---|
sum_range | Required | Numeric column you want to total. Must be the same size as every criteria range. |
criteria_range1 | Required | The first column you want to filter on. |
criteria1 | Required | Text and operators in double quotes: "East", ">1000". |
criteria_range2, criteria2 | Optional | Up to 127 pairs total, all combined with AND. |
People carry the SUMIF habit into SUMIFS and write =SUMIFS(A2:A100,"East",C2:C100). Excel happily returns a number — usually 0 or the wrong total — because the arguments are interpreted as valid but misaligned. If your SUMIFS returns a suspicious result, check the argument order first.
=SUMIFS(C2:C100,A2:A100,East) makes Excel look for a defined name called East. It fails with #NAME?. Use "East" instead.
Criteria Operators and Wildcards
Each criteria argument follows the same rules as SUMIF criteria. You can use comparison operators, text, numbers, dates, wildcards, and cell references — and each pair is combined with AND.
The six comparison operators
| Operator | Meaning | Example criteria | Matches when… |
|---|---|---|---|
= | Equal to | "East" | Cell equals East |
> | Greater than | ">100" | Cell is above 100 |
< | Less than | "<0" | Cell is negative |
>= | Greater or equal | ">=1000" | Cell is 1000 or more |
<= | Less or equal | "<=50" | Cell is 50 or less |
<> | Not equal to | "<>East" | Cell is not East |
Wildcard characters
Three wildcards work when the criteria is text. They are useful for partial matches without knowing the exact value.
Any number of characters
"North*" matches North, Northeast, Northwest. Use it when the text starts, ends, or contains a phrase.
Exactly one character
"A?C" matches ABC, AXC, and A1C. Use it for fixed-length codes with one unknown character.
Escape a wildcard
"~*" matches a literal asterisk. Use it when your data actually contains * or ?.
' Text match plus numeric filter
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">1000")
' Cell reference as criteria
=SUMIFS(C2:C100, A2:A100, E2, B2:B100, F2)
' Operator plus cell reference
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">"&E2)
' Wildcard: starts with North
=SUMIFS(C2:C100, A2:A100, "North*")
' Not blank and not East
=SUMIFS(C2:C100, A2:A100, "<>", B2:B100, "<>East")
' Blank cells only (empty string)
=SUMIFS(C2:C100, A2:A100, "")
If E2 contains 1000, write ">"&E2. Writing ">E2" looks for the literal text “>E2” and returns 0.
Real-World Examples
These five patterns cover most day-to-day SUMIFS use. Each one shows the data layout, the formula, and the result you should see.
Example 1 — Sales by region and month
The classic two-condition total: filter on a text column and a date column at the same time.
| A Date | B Region | C Sales | |
|---|---|---|---|
| 2 | 2026-01-05 | East | 1200 |
| 3 | 2026-01-18 | West | 900 |
| 4 | 2026-01-22 | East | 1500 |
| 5 | 2026-02-02 | East | 800 |
| 6 | 2026-01-28 | East | 1100 |
| 7 | 2026-02-14 | North | 700 |
=SUMIFS(C2:C7, A2:A7, ">="&DATE(2026,1,1), A2:A7, "<="&DATE(2026,1,31), B2:B7, "East")Result: 1200 + 1500 + 1100 = 3800
Example 2 — Expenses by category and amount threshold
| A Date | B Category | C Amount | |
|---|---|---|---|
| 2 | 2026-01-05 | Travel | 420 |
| 3 | 2026-01-08 | Travel | 1250 |
| 4 | 2026-01-12 | Software | 380 |
| 5 | 2026-01-15 | Travel | 2900 |
| 6 | 2026-01-20 | Office | 150 |
=SUMIFS(C2:C6, B2:B6, "Travel", C2:C6, ">1000")Result: 1250 + 2900 = 4150 — the small travel rows are excluded.
Example 3 — Invoice aging by customer
Two text conditions plus one date condition: three pairs in one formula.
' Overdue amounts for Acme, 30+ days late, still unpaid
=SUMIFS(D2:D200,
A2:A200, "Acme",
B2:B200, ">30",
C2:C200, "Open")
Example 4 — Between two dates
A date range is always two criteria pairs on the same criteria range — one for the lower bound, one for the upper bound.
' Q1 2026 total for East
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,1,1),
A2:A100, "<="&DATE(2026,3,31),
B2:B100, "East")
If your dates are stored as text, SUMIFS often returns 0. Convert them with DATEVALUE or re-enter them as real dates.
Example 5 — Excluding a value with <>
Sometimes the condition is negative: “everything except X”. Use <> to exclude.
' Every region except East
=SUMIFS(C2:C100, B2:B100, "<>East")
' Every row where the customer field is not blank
=SUMIFS(C2:C100, A2:A100, "<>")
' Every product code that is not a discontinued prefix
=SUMIFS(C2:C100, A2:A100, "<>DC-*")
SUMIFS vs SUMIF — Same Job, Different Argument Order
The two functions look nearly identical and often produce the same result. The single difference that matters is where the sum range goes. Get that wrong and the formula returns a plausible but incorrect number without any error.
=SUMIF(A2:A20,"East",B2:B20)=SUMIFS(B2:B20,
A2:A20,"East")| Function | Best for | Argument order | Introduced |
|---|---|---|---|
SUMIF | Exactly one condition | range, criteria, [sum_range] | Excel 97 |
SUMIFS | One or more conditions | sum_range, criteria_range1, criteria1, … | Excel 2007 |
The two formulas side by side
' SUMIF — one condition
=SUMIF(A2:A100,"East",C2:C100)
' SUMIFS — the same result, argument order inverted
=SUMIFS(C2:C100, A2:A100, "East")
' SUMIFS — the reason it exists: many conditions
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">1000", D2:D100, "Open")
Train your fingers to type =SUMIFS( even when you only need one condition. If you later add a second condition, the formula is already in the right shape — you just extend it.
SUMIFS with Dates — Monthly, Quarterly, and YTD Totals
Date-based totals are the single most common use of SUMIFS in real reports. Sales by month, expenses by quarter, running totals for the year — they all come down to the same pattern: two date pairs on the same column.
Pattern 1 — Monthly total
- Pick the month start. Use
DATE(2026,1,1)for January 2026. - Pick the month end. Use
EOMONTH(DATE(2026,1,1),0). It returns 31 Jan 2026 without you needing to know how many days are in the month. - Add the two criteria pairs to the same range. One for
>= start, one for<= end.
' January 2026 total — all regions
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,1,1),
A2:A100, "<="&EOMONTH(DATE(2026,1,1),0))
' January 2026 total — East only
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,1,1),
A2:A100, "<="&EOMONTH(DATE(2026,1,1),0),
B2:B100, "East")
Pattern 2 — Quarterly total
A quarter is just a wider date range. Q1 runs from 1 Jan to 31 Mar. Use DATE() for the boundaries so Excel never misreads a text date.
' Q1 2026 (Jan 1 – Mar 31)
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,1,1),
A2:A100, "<="&DATE(2026,3,31))
' Q2 2026 (Apr 1 – Jun 30)
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,4,1),
A2:A100, "<="&DATE(2026,6,30))
Pattern 3 — Year-to-date (YTD)
YTD is a single-sided range: everything from 1 Jan of the current year up to today. One SUMIFS condition is enough.
' YTD using TODAY() — always current
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(YEAR(TODAY()),1,1),
A2:A100, "<="&TODAY())
' YTD for a fixed year (2026)
=SUMIFS(C2:C100,
A2:A100, ">="&DATE(2026,1,1),
A2:A100, "<="&DATE(2026,12,31))
Pattern 4 — Dynamic month driven by a cell
Once you have a working month formula, make it flexible by driving the start date from a cell. The whole report updates when you change one value.
| A | B | C | |
|---|---|---|---|
| 1 | Month start | 2026-01-01 | |
| 2 | Month end | =EOMONTH(B1,0) | |
| 3 | Total | 4600 | =SUMIFS(C10:C200, A10:A200,”>=”&B1, A10:A200,”<=”&B2) |
2026-02-01 and both the month-end and the total recalculate automatically.Instead of typing DATE(2026,1,1) and DATE(2026,1,31), put the month start in one cell and use =EOMONTH(E2,0) for the end. Change the start and the whole report follows.
If column A shows dates but they are stored as text (left-aligned in the cell, no date formatting), >=DATE(...) will never match, and the formula quietly returns 0. Verify with =ISNUMBER(A2) — a real date returns TRUE.
OR Logic in SUMIFS — The Workarounds
SUMIFS combines every criteria pair with AND. There is no built-in way to say “East OR West”. This is the most common limitation people hit, and there are three reliable workarounds.
Workaround 1 — Add two SUMIFS together
Simple, readable, and works in every Excel version. Use this when you have a small fixed list of values.
' East OR West
=SUMIFS(C2:C100, A2:A100, "East")
+SUMIFS(C2:C100, A2:A100, "West")
' With an additional AND condition applied to both
=SUMIFS(C2:C100, A2:A100, "East", D2:D100, "Open")
+SUMIFS(C2:C100, A2:A100, "West", D2:D100, "Open")
Workaround 2 — An array constant inside the criteria
Wrap the OR values in curly braces, then wrap the whole thing in SUM. It reads like one formula and scales to a longer list.
' East OR West, wrapped in SUM
=SUM(SUMIFS(C2:C100, A2:A100, {"East","West"}))
' Three regions in one formula
=SUM(SUMIFS(C2:C100, A2:A100, {"East","West","North"}))
The array constant makes SUMIFS return two results, one per value in the array. Wrapping it in SUM() adds them together into a single number. Without the wrapper you will see a spilled pair of values, or a #VALUE! in older Excel.
Workaround 3 — SUMPRODUCT for complex OR with AND
When the OR list is long or the logic is nested — “(East OR West) AND (Q1 OR Q2)” — SUMPRODUCT with + for OR and * for AND is the most flexible tool.
' (East OR West) AND amount > 1000
=SUMPRODUCT(
((A2:A100="East")+(A2:A100="West")) * (C2:C100>1000) * C2:C100
)
' (East OR West) AND (Q1 OR Q2) — nested ORs
=SUMPRODUCT(
((A2:A100="East")+(A2:A100="West")) *
((B2:B100>=DATE(2026,1,1))*(B2:B100<=DATE(2026,3,31)) +
(B2:B100>=DATE(2026,4,1))*(B2:B100<=DATE(2026,6,30))) *
C2:C100
)
=SUMIFS(C:C,A:A,"East")
+SUMIFS(C:C,A:A,"West")=SUM(SUMIFS(C:C,A:A,
{"East","West"}))"East*" looks like it might match “East” and “Eastern” — but it also matches “East Coast”, “Eastside Logistics”, and anything else that happens to start with those letters. Use an explicit array constant or add two formulas.
SUMIFS Across Multiple Sheets and Workbooks
Most real workbooks are not one giant table. They are one sheet per month, one sheet per region, or one file per department. SUMIFS can reach across all of them — with the same rules as SUMIF plus a few extra things to watch for.
Referencing another sheet
Prefix every range with the sheet name and an exclamation mark: SheetName!A2:A100. If the sheet name has spaces, wrap it in single quotes: 'Jan 2026'!A2:A100.
' East sales over 1000, all from the Jan sheet
=SUMIFS(Jan!C2:C1000,
Jan!A2:A1000, "East",
Jan!B2:B1000, ">1000")
' Sheet name with a space needs single quotes
=SUMIFS('Jan 2026'!C2:C1000,
'Jan 2026'!A2:A1000, "East")
Combining several sheets into one total
SUMIFS does not accept a true 3D reference like Jan:Dec!A2:A100. The reliable pattern is to add one SUMIFS per sheet.
' East sales over 1000 across Jan, Feb, Mar
=SUMIFS(Jan!C:C, Jan!A:A, "East", Jan!B:B, ">1000")
+SUMIFS(Feb!C:C, Feb!A:A, "East", Feb!B:B, ">1000")
+SUMIFS(Mar!C:C, Mar!A:A, "East", Mar!B:B, ">1000")
Every Jan!A:A scans the entire column, even the million empty rows. If you have ten sheets and ten rows of formulas, that is a hundred full-column scans and Excel will feel it. Use bounded ranges like Jan!A2:A1000, or Excel Tables with structured references.
Referencing a sheet whose name is in a cell
If you want the sheet name to be dynamic — for example a drop-down in cell E1 that switches between Jan, Feb, and Mar — use INDIRECT.
' E1 contains the sheet name, e.g. "Feb"
=SUMIFS(INDIRECT(E1&"!C2:C1000"),
INDIRECT(E1&"!A2:A1000"), "East",
INDIRECT(E1&"!B2:B1000"), ">1000")
Volatile means it recalculates on every change anywhere in the workbook, which slows large files down. It also does not survive a sheet rename — the formula just breaks. Use it sparingly and document it.
Referencing another workbook
Same idea, but with the file name in square brackets. Closed workbooks must be referenced with the full path.
' Referencing an open workbook
=SUMIFS([Sales.xlsx]Jan!C:C,
[Sales.xlsx]Jan!A:A, "East",
[Sales.xlsx]Jan!B:B, ">1000")
' Referencing a closed workbook on disk
=SUMIFS('C:\Reports\[Sales.xlsx]Jan'!C:C,
'C:\Reports\[Sales.xlsx]Jan'!A:A, "East")
SUMIFS vs SUMPRODUCT vs FILTER vs PivotTable
SUMIFS is excellent at one thing: a fixed multi-condition total in a specific cell. It is the wrong tool for several other jobs, and knowing when to switch saves hours of workarounds.
| Tool | Best for | Watch out for |
|---|---|---|
SUMIFS | Two or more conditions, all combined with AND | Cannot express OR inside a single pair — see the OR logic workarounds. |
SUMIF | Exactly one condition, older compatibility | Different argument order from SUMIFS. Easy to mix up. |
SUMPRODUCT | OR logic, case-sensitive matching, or conditions on calculated values | Array-based and heavier on large ranges; syntax is less forgiving. |
FILTER + SUM | Spilling a filtered table, then summing it | Microsoft 365 and Excel 2021+ only. |
PivotTable | Exploring totals from many angles interactively | Needs a refresh to pick up new data; not a live cell value. |
Same question, four tools
“What was East’s total for amounts over 1000, in Q1?” — two AND conditions plus a date range. Here is how each tool answers it.
' 1. SUMIFS — the normal answer
=SUMIFS(C2:C100,
A2:A100, "East",
B2:B100, ">1000",
D2:D100, ">="&DATE(2026,1,1),
D2:D100, "<="&DATE(2026,3,31))
' 2. SUMPRODUCT — needed when logic goes beyond AND
=SUMPRODUCT(
(A2:A100="East") * (B2:B100>1000) *
(D2:D100>=DATE(2026,1,1)) * (D2:D100<=DATE(2026,3,31)) *
C2:C100
)
' 3. FILTER + SUM — reads like a sentence (M365 / 2021)
=SUM(FILTER(C2:C100,
(A2:A100="East") * (B2:B100>1000) *
(D2:D100>=DATE(2026,1,1)) * (D2:D100<=DATE(2026,3,31))))
' 4. PivotTable — drag Region and Date to Rows, Amount to Values,
' then set a Value Filter of "greater than 1000"
When SUMIFS is the wrong choice
“East OR West”
SUMIFS combines with AND only. Add two SUMIFS, use an array constant, or switch to SUMPRODUCT with + between conditions.
“Match exactly ‘EAST’”
SUMIFS is case-insensitive. Use SUMPRODUCT(--EXACT(A2:A100,"EAST"),C2:C100).
“Where Month(sale) = 3”
SUMIFS cannot test a formula against a range. Add a helper column with the month, or use SUMPRODUCT with MONTH() on the range.
“Show me everything for East”
If the answer is a list, not a single number, use FILTER (M365) or a PivotTable — not SUMIFS.
Two or more AND conditions → SUMIFS. OR logic, case sensitivity, or formula-based conditions → SUMPRODUCT. A whole filtered list → FILTER or PivotTable. One condition on older Excel → SUMIF.
Build a Sales Dashboard with SUMIFS
Everything up to this point comes together here: one raw data sheet, one summary sheet, and a handful of SUMIFS formulas that update automatically when new rows arrive.
Step 1 — Lay out the source data as a Table
Select the data, then press Ctrl+T. Tables give you automatic named ranges that grow when you add rows, so your dashboard never needs to be edited again.
| A Date | B Region | C Rep | D Amount | |
|---|---|---|---|---|
| 2 | 2026-01-05 | East | Ana | 1200 |
| 3 | 2026-01-18 | West | Ben | 900 |
| 4 | 2026-02-02 | East | Carla | 1500 |
| 5 | 2026-02-19 | East | Ana | 800 |
| 6 | 2026-03-08 | North | Ben | 700 |
| 7 | 2026-03-22 | East | Carla | 1100 |
SalesData. All formulas below use SalesData instead of cell ranges.Step 2 — Create a summary sheet with the headings
On a new sheet, build this small grid. The Region cells will hold the criteria; the values come from SUMIFS.
| A Region | B Q1 Total | C YTD Total | D Orders | |
|---|---|---|---|---|
| 2 | East | 4600 | 4600 | 4 |
| 3 | West | 900 | 900 | 1 |
| 4 | North | 700 | 700 | 1 |
| 5 | South | 0 | 0 | 0 |
Step 3 — Write the formulas
- Q1 Total (cell B2): two date bounds plus the region test.
=SUMIFS(SalesData[Amount], SalesData[Date], ">="&DATE(2026,1,1), SalesData[Date], "<="&DATE(2026,3,31), SalesData[Region], $A2) - YTD Total (cell C2): one-sided date range from Jan 1 to today.
=SUMIFS(SalesData[Amount], SalesData[Date], ">="&DATE(YEAR(TODAY()),1,1), SalesData[Date], "<="&TODAY(), SalesData[Region], $A2) - Orders (cell D2): same shape, but count instead of sum.
=COUNTIFS(SalesData[Region], $A2, SalesData[Date], ">="&DATE(2026,1,1)) - Copy B2:D2 down to row 5. The
$A2reference is locked to column A but free in the row, so each region picks up its own total.
Step 4 — Add a grand total that never needs updating
Because the source is a Table, the totals stay correct as rows are added. The dashboard row is simply a summary of the summary.
' Total sales across all regions
=SUM(SalesData[Amount])
' Total only for Q1
=SUMIFS(SalesData[Amount],
SalesData[Date], ">="&DATE(2026,1,1),
SalesData[Date], "<="&DATE(2026,3,31))
' East's share of Q1 (percentage)
=B2 / SUM(SalesData[Amount])
Three decisions make the dashboard durable: the source is a Table (grows automatically), every criterion lives in a cell (editable data, not hidden in a formula), and every date bound is built with DATE() (immune to regional date formats).
If you rename SalesData to Sales, every formula breaks with #REF!. Keep the name stable, or use Find & Replace to update all at once.
Common Errors and How to Fix Them
Most SUMIFS problems fall into one of a handful of patterns. Each one below has a known cause and a known fix.
The sum_range and one or more criteria ranges have different heights or widths. Excel cannot align them and returns #VALUE!.
Fix: make every range exactly the same size. Prefer Tables or structured references so this cannot happen.
You wrote =SUMIFS(C2:C10,A2:A10,East). Excel looks for a defined name called East and finds nothing.
Fix: wrap text criteria in double quotes: "East".
You carried the SUMIF habit into SUMIFS and put the range first. Excel still returns a number, but it is not the number you asked for.
Fix: the first argument must be sum_range. Check the formula against the syntax above.
Four common causes: no row satisfies every condition, numbers stored as text, dates stored as text, or extra spaces around text values.
Fix: clean the data with TRIM and VALUE, or re-enter the values in the correct type.
Your sum_range starts at row 2 but a criteria_range starts at row 1. The totals look plausible but are silently wrong.
Fix: anchor every range to the same starting row and ending row.
Your data contains a literal * or ? and Excel treats it as a wildcard.
Fix: escape it with a tilde, for example "~*".
SUMIFS is not case-sensitive. To sum only exact-case matches, use SUMPRODUCT with EXACT.
' SUMIFS treats "East" and "east" the same
=SUMIFS(C2:C100, A2:A100, "East")
' Case-sensitive sum
=SUMPRODUCT(--EXACT(A2:A100,"East"), C2:C100)
Best Practices for SUMIFS
These habits keep conditional totals accurate, fast, and easy to audit months later.
Use Excel Tables
Structured references grow automatically and make formulas easier to read.
Put criteria in cells
A criterion buried in a formula is invisible. A cell reference is editable data.
Match every range size
All ranges must have the same height and width, or Excel returns #VALUE!.
Anchors on the same row
Every range should start and end on the same rows. Off-by-one anchors are silent bugs.
Remember: sum_range first
The single most common mistake. Type =SUMIFS( and start with the value column.
Use real dates
Text dates are one of the top causes of zero totals. Convert them to real date values.
Escape wildcards when needed
Use ~* and ~? to match literal wildcard characters in your data.
Check for text numbers
Numbers stored as text are ignored. Convert with VALUE or Text to Columns.
Bound your ranges
Prefer A2:A10000 over A:A on multi-sheet formulas for speed.
Avoid INDIRECT unless you must
It is volatile and breaks on sheet renames. Prefer direct references.
Practice Questions
Try these before opening the answers. They use the examples from earlier in the guide.
- Q1. What is the argument order for SUMIFS?
Answer:=SUMIFS(sum_range, criteria_range1, criteria1, …). The sum range comes first. - Q2. Why does
=SUMIFS(A2:A10,"East",C2:C10)return the wrong result?
Answer: The argument order is wrong.sum_rangemust come first:=SUMIFS(C2:C10, A2:A10, "East"). - Q3. How do you sum values between two dates in SUMIFS?
Answer: Use two criteria pairs on the same date range:=SUMIFS(C2:C100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&DATE(2026,12,31)). - Q4. Can SUMIFS handle OR logic directly?
Answer: No. Every criteria pair is combined with AND. For OR, add two SUMIFS formulas together, or wrap an array constant in SUM. - Q5. What does
=SUM(SUMIFS(C2:C100,A2:A100,{"East","West"}))do?
Answer: It sums C2:C100 for rows where the region is East or West. The array constant causes SUMIFS to return two results, and SUM() adds them together. - Q6. Why is
INDIRECTdiscouraged in large workbooks?
Answer: It is volatile — it recalculates on every change anywhere in the workbook — and it breaks when a referenced sheet is renamed.
Frequently Asked Questions
What is the SUMIFS function in Excel?
SUMIFS adds the values in a range that meet multiple conditions. Its syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
How is SUMIFS different from SUMIF?
SUMIF handles one condition and puts the range first. SUMIFS handles many conditions and puts sum_range first, followed by pairs of criteria_range and criteria.
How many criteria can SUMIFS handle?
Up to 127 criteria pairs, for a total of 254 arguments plus sum_range.
Do all ranges in SUMIFS need to be the same size?
Yes. Every criteria range must have the same number of rows and columns as sum_range, or Excel returns #VALUE!.
Can SUMIFS use OR logic?
Not directly. SUMIFS combines conditions with AND. For OR logic, add two SUMIFS together, or use an array constant inside the criteria — for example =SUM(SUMIFS(C:C,A:A,{"East","West"})).
Can SUMIFS use wildcards?
Yes. Use * for any number of characters, ? for a single character, and ~ to escape a wildcard.
Is SUMIFS case-sensitive?
No. SUMIFS is not case-sensitive. For case-sensitive matching, use SUMPRODUCT with EXACT.
How do I sum between two dates with SUMIFS?
Use two criteria on the same date range: =SUMIFS(C2:C100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&DATE(2026,12,31)).
Why does my SUMIFS return 0?
Common causes include no matching rows, text numbers, text dates, extra spaces in text values, or criteria ranges that are offset by one row from sum_range.
Does SUMIFS work across worksheets?
Yes. Prefix each range with the sheet name: =SUMIFS(Jan!C:C, Jan!A:A, "East", Jan!B:B, ">1000"). Add multiple SUMIFS formulas together to combine sheets.
Key Takeaways
- SUMIFS adds values that meet multiple conditions.
- The syntax is
=SUMIFS(sum_range, criteria_range1, criteria1, …). - sum_range comes first — the opposite of SUMIF. This is the #1 mistake.
- Every range must be exactly the same size and align on the same rows.
- Up to 127 criteria pairs, all combined with AND.
- Text and operators in criteria must be in double quotes.
- Use
&to combine an operator with a cell reference:">"&E2. - Wildcards
*and?work in text criteria; escape them with~. - SUMIFS is not case-sensitive.
- For dates, pair
DATE()withEOMONTH()for month ranges and today for YTD. - For OR logic, add two SUMIFS together, or wrap
{...}inSUM(). - Cross-sheet SUMIFS uses
SheetName!Range; add formulas together to combine sheets. - Reach for SUMPRODUCT or FILTER when the logic goes beyond AND.
- Build dashboards on Excel Tables so ranges grow automatically.
- For case-sensitive sums, use
SUMPRODUCTwithEXACT.
What to Learn Next
SUMIFS is the gateway to Excel’s conditional calculation family. Once it is comfortable, the natural next steps are the functions that count, average, and look up under the same conditions.
Start with the functions that pair with SUMIFS — sumif-function, countifs-function, and averageifs-function.
From there, move into multi-condition lookups and modern dynamic tools: sumproduct-function, maxifs-function, filter-function, and xlookup-function.