SUMIFS Function in Excel: Ultimate Guide

SUMIFS Function in Excel: Ultimate Guide
IL Inar Learn
SUMIFS Function in Excel — Ultimate Guide Saved
18 min read Excel 2019 · 2021 · Microsoft 365 Updated for 2026

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.

In one sentence

SUMIFS adds the values in a range that meet multiple conditions, written as =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

01

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.

FunctionSUMIFS
CategoryMath & Trig
Arguments3–255
Max criteria pairs127
SinceExcel 2007

How SUMIFS evaluates a formula

Take the formula =SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">1000"). Excel does three things, in order:

  1. Test the first criteria pair. It reads every cell in A2:A100 and asks: is this cell “East”?
  2. Test every additional pair. It reads every cell in B2:B100 and asks: is this cell greater than 1000? A row must pass every test to survive.
  3. Add the sum range. It adds the values in C2:C100 for the surviving rows only. Rows that fail any test are ignored.
i
SUMIFS combines conditions with AND, not OR

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.

✓
Use SUMIFS even for a single condition

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.

02

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.

Formula syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Walking through each argument

  1. sum_range — the cells you want to add. Comes first, before any criteria. This is the opposite of SUMIF.
  2. criteria_range1 — the cells you want to test against the first condition. Must be the same size as sum_range.
  3. criteria1 — the condition to match. Text and operators must be in double quotes.
  4. criteria_range2, criteria2 … (optional) — additional pairs, up to 127 total. Every pair is combined with AND.
Argument reference
ArgumentRequired?Notes
sum_rangeRequiredNumeric column you want to total. Must be the same size as every criteria range.
criteria_range1RequiredThe first column you want to filter on.
criteria1RequiredText and operators in double quotes: "East", ">1000".
criteria_range2, criteria2OptionalUp to 127 pairs total, all combined with AND.
!
The most common mistake: sum_range is not first

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.

!
Text needs double quotes

=SUMIFS(C2:C100,A2:A100,East) makes Excel look for a defined name called East. It fails with #NAME?. Use "East" instead.

03

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

Operators you can place inside a SUMIFS criteria argument
OperatorMeaningExample criteriaMatches 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 ?.

Criteria examples
' 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, "")
✓
Use & to combine an operator with a cell reference

If E2 contains 1000, write ">"&E2. Writing ">E2" looks for the literal text “>E2” and returns 0.

04

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.

Sales DataSales.xlsx
A
Date
B
Region
C
Sales
22026-01-05East1200
32026-01-18West900
42026-01-22East1500
52026-02-02East800
62026-01-28East1100
72026-02-14North700
Formula: =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

ExpensesExpenses.xlsx
A
Date
B
Category
C
Amount
22026-01-05Travel420
32026-01-08Travel1250
42026-01-12Software380
52026-01-15Travel2900
62026-01-20Office150
Formula: =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.

Three-condition total
' 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.

Date range
' Q1 2026 total for East
=SUMIFS(C2:C100,
        A2:A100, ">="&DATE(2026,1,1),
        A2:A100, "<="&DATE(2026,3,31),
        B2:B100, "East")
!
Dates must be real dates

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.

Exclusion criteria
' 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-*")
05

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 — range first
=SUMIF(A2:A20,"East",B2:B20)
Argument order: range, criteria, sum_range. One condition only.
SUMIFS — sum_range first
=SUMIFS(B2:B20, A2:A20,"East")
Argument order: sum_range, criteria_range, criteria. Unlimited pairs.
Choosing between SUMIF and SUMIFS
FunctionBest forArgument orderIntroduced
SUMIFExactly one conditionrange, criteria, [sum_range]Excel 97
SUMIFSOne or more conditionssum_range, criteria_range1, criteria1, …Excel 2007

The two formulas side by side

Equivalent formulas
' 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")
✓
One habit that avoids the whole problem

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.

06

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

  1. Pick the month start. Use DATE(2026,1,1) for January 2026.
  2. 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.
  3. Add the two criteria pairs to the same range. One for >= start, one for <= end.
Monthly totals
' 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.

Quarterly totals
' 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.

Year-to-date totals
' 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.

Dynamic Month SelectorReport.xlsx
ABC
1Month start2026-01-01
2Month end=EOMONTH(B1,0)
3Total4600=SUMIFS(C10:C200, A10:A200,”>=”&B1, A10:A200,”<=”&B2)
Change B1 to 2026-02-01 and both the month-end and the total recalculate automatically.
✓
Make month boundaries dynamic with EOMONTH

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.

✕
Text dates break SUMIFS silently

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.

07

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.

Adding SUMIFS for OR
' 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.

Array constant for OR
' 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"}))
✓
Why SUM() is required here

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.

Complex OR with SUMPRODUCT
' (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
)
Readable and version-safe
=SUMIFS(C:C,A:A,"East") +SUMIFS(C:C,A:A,"West")
Two clear formulas added together. Any Excel version, any user, easy to audit.
One line, arrays
=SUM(SUMIFS(C:C,A:A, {"East","West"}))
Compact and scalable. Requires understanding of array constants and the SUM() wrapper.
!
Do not use a wildcard to fake an OR

"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.

08

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.

Cross-sheet SUMIFS
' 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.

Total across three sheets
' 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")
!
Whole-column references across sheets add up fast

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.

Dynamic sheet reference with INDIRECT
' E1 contains the sheet name, e.g. "Feb"
=SUMIFS(INDIRECT(E1&"!C2:C1000"),
        INDIRECT(E1&"!A2:A1000"), "East",
        INDIRECT(E1&"!B2:B1000"), ">1000")
✕
INDIRECT is volatile and will not track renames

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.

Cross-workbook SUMIFS
' 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")
09

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.

Which tool to reach for
ToolBest forWatch out for
SUMIFSTwo or more conditions, all combined with ANDCannot express OR inside a single pair — see the OR logic workarounds.
SUMIFExactly one condition, older compatibilityDifferent argument order from SUMIFS. Easy to mix up.
SUMPRODUCTOR logic, case-sensitive matching, or conditions on calculated valuesArray-based and heavier on large ranges; syntax is less forgiving.
FILTER + SUMSpilling a filtered table, then summing itMicrosoft 365 and Excel 2021+ only.
PivotTableExploring totals from many angles interactivelyNeeds 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.

Four ways to sum the same data
' 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

OR

“East OR West”

SUMIFS combines with AND only. Add two SUMIFS, use an array constant, or switch to SUMPRODUCT with + between conditions.

CASE

“Match exactly ‘EAST’”

SUMIFS is case-insensitive. Use SUMPRODUCT(--EXACT(A2:A100,"EAST"),C2:C100).

CALC

“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.

LIVE

“Show me everything for East”

If the answer is a list, not a single number, use FILTER (M365) or a PivotTable — not SUMIFS.

i
Rule of thumb

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.

10

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.

Source — Table1Dashboard.xlsx
A
Date
B
Region
C
Rep
D
Amount
22026-01-05EastAna1200
32026-01-18WestBen900
42026-02-02EastCarla1500
52026-02-19EastAna800
62026-03-08NorthBen700
72026-03-22EastCarla1100
Table name: after creating the Table, change its name in the Table Design tab to 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.

Summary sheetDashboard.xlsx
A
Region
B
Q1 Total
C
YTD Total
D
Orders
2East460046004
3West9009001
4North7007001
5South000
Each cell in columns B, C and D is a single formula copied down.

Step 3 — Write the formulas

  1. 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)
  2. 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)
  3. Orders (cell D2): same shape, but count instead of sum.
    =COUNTIFS(SalesData[Region], $A2, SalesData[Date], ">="&DATE(2026,1,1))
  4. Copy B2:D2 down to row 5. The $A2 reference 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.

Dashboard footer formulas
' 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])
✓
Why this pattern stays reliable

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).

!
Structured references need the exact table name

If you rename SalesData to Sales, every formula breaks with #REF!. Keep the name stable, or use Find & Replace to update all at once.

11

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.

✕
#VALUE! — mismatched range sizes

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.

✕
#NAME? — unquoted text

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".

!
Wrong total — argument order swapped

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.

!
Returns 0 — nothing matched

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.

!
Criteria ranges offset by one row

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.

!
Wildcard not matching what you expect

Your data contains a literal * or ? and Excel treats it as a wildcard.

Fix: escape it with a tilde, for example "~*".

i
Case-sensitive match needed

SUMIFS is not case-sensitive. To sum only exact-case matches, use SUMPRODUCT with EXACT.

Case-sensitive alternative
' SUMIFS treats "East" and "east" the same
=SUMIFS(C2:C100, A2:A100, "East")

' Case-sensitive sum
=SUMPRODUCT(--EXACT(A2:A100,"East"), C2:C100)
12

Best Practices for SUMIFS

These habits keep conditional totals accurate, fast, and easy to audit months later.

01

Use Excel Tables

Structured references grow automatically and make formulas easier to read.

02

Put criteria in cells

A criterion buried in a formula is invisible. A cell reference is editable data.

03

Match every range size

All ranges must have the same height and width, or Excel returns #VALUE!.

04

Anchors on the same row

Every range should start and end on the same rows. Off-by-one anchors are silent bugs.

05

Remember: sum_range first

The single most common mistake. Type =SUMIFS( and start with the value column.

06

Use real dates

Text dates are one of the top causes of zero totals. Convert them to real date values.

07

Escape wildcards when needed

Use ~* and ~? to match literal wildcard characters in your data.

08

Check for text numbers

Numbers stored as text are ignored. Convert with VALUE or Text to Columns.

09

Bound your ranges

Prefer A2:A10000 over A:A on multi-sheet formulas for speed.

10

Avoid INDIRECT unless you must

It is volatile and breaks on sheet renames. Prefer direct references.

13

Practice Questions

Try these before opening the answers. They use the examples from earlier in the guide.

  1. Q1. What is the argument order for SUMIFS?
    Answer: =SUMIFS(sum_range, criteria_range1, criteria1, …). The sum range comes first.
  2. Q2. Why does =SUMIFS(A2:A10,"East",C2:C10) return the wrong result?
    Answer: The argument order is wrong. sum_range must come first: =SUMIFS(C2:C10, A2:A10, "East").
  3. 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)).
  4. 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.
  5. 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.
  6. Q6. Why is INDIRECT discouraged 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.
14

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.

15

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() with EOMONTH() for month ranges and today for YTD.
  • For OR logic, add two SUMIFS together, or wrap {...} in SUM().
  • 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 SUMPRODUCT with EXACT.
16

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.

Inar Learn
Inar Learnhttps://inarlearn.com
Inar Learn is an innovative online learning platform offering high-quality courses, tutorials, and resources to help learners gain practical skills and grow their knowledge.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here