SUMIF and SUMIFS Explained: Criteria Syntax, Examples, and Differences

SUMIF sums the cells that meet one condition; SUMIFS sums cells that meet multiple conditions at once (AND logic). Together they replace most manual filtering-and-totaling work in Excel.

What's the difference between SUMIF and SUMIFS?

SUMIF handles exactly one condition; SUMIFS handles up to 127 conditions that must ALL be true. The trap is the argument order: SUMIF puts the sum range last (and optional), while SUMIFS puts it first (and required). Swapping them is the single most common cause of wrong SUMIFS results.

Aspect	SUMIF	SUMIFS
Conditions	One condition	Multiple (AND logic)
Argument order	range, criteria, sum_range	sum_range, range1, crit1, ...
sum_range position	Optional 3rd argument (LAST)	Required 1st argument (FIRST)
Use when	Single condition	Multiple conditions — or always, for consistency

The argument-order trap, side by side

Same job — sum Apple sales — both ways:

SUMIF  (test range first, sum range last):
=SUMIF(A2:A10, "Apple", B2:B10)

SUMIFS (sum range FIRST, then pairs):
=SUMIFS(B2:B10, A2:A10, "Apple")

WRONG (SUMIF order fed to SUMIFS):
=SUMIFS(A2:A10, "Apple", B2:B10)   → error, not a total

Tip: many pros use SUMIFS even for one condition,
so every formula shares the same argument order.

If you only need the head-to-head comparison, including which function to default to, the shorter SUMIF vs SUMIFS comparison covers just that. The rest of this page explains how each function works and how to write its criteria.

How does SUMIF work? (SUMIF explained)

SUMIF tests each cell in range against the criteria and, for every cell that passes, adds the value in the matching position of sum_range. SUM vs SUMIF: SUM adds every number in a range, SUMIF adds only the ones whose row passes the test. Leave sum_range out and SUMIF adds the tested cells themselves, so =SUMIF(B2:B10, ">100") totals only the values above 100.

=SUMIF(range, criteria, [sum_range])

range: Where to check the condition
criteria: What to look for
sum_range: What to sum (optional, defaults to range)

How do I write SUMIF criteria? (Syntax table)

Criteria are text strings, so quotes matter: plain values match exactly, comparison operators live inside the quotes, cell references are concatenated on with &, wildcards * and ? do partial matching, and dates are safest built with the DATE function rather than typed as regional strings.

Criteria type	Example	What it matches
Text (exact)	"Apple"	Cells equal to Apple (not case-sensitive)
Number	100	Cells equal to 100 (no quotes needed)
Comparison	">100", "<=50", "<>0"	Greater than, at most, not equal
Cell reference	">"&D1	Greater than the value in D1
Wildcard: any chars	"*west*"	Contains "west" anywhere
Wildcard: one char	"SKU-??"	SKU- plus exactly two characters
Date (safe form)	">="&DATE(2026,1,1)	On or after Jan 1, 2026 — locale-proof
Blank / non-blank	"" / "<>"	Empty cells / non-empty cells

SUMIF Examples

Every example below uses one condition. What varies is how the criteria is written: a plain value, an operator inside quotes, a bare cell reference, or an operator joined to a cell reference with &.

Basic Example

Data:
A          B
Product    Sales
Apple      100
Orange     150
Apple      200
Banana     120

Sum all Apple sales:
=SUMIF(A:A, "Apple", B:B)
Result: 300

With Comparison Operators

=SUMIF(B2:B10, ">100", B2:B10)
Sum values greater than 100

=SUMIF(B2:B10, ">=50")
Sum values 50 or more (sum_range optional when same as range)

=SUMIF(B2:B10, "<>"&0, B2:B10)
Sum all non-zero values

With Cell Reference

Target in D1: 50

=SUMIF(B2:B10, ">"&D1, B2:B10)
Sum values greater than D1

=SUMIF(A2:A10, C2, B2:B10)
Sum where column A matches C2

How does SUMIFS work? (SUMIFS explained)

SUMIFS adds a value from sum_range only when its row passes every condition: each criteria_range is tested against its own criteria, and all of the tests must be true (AND logic). Every criteria_range must be the same size and shape as sum_range, or SUMIFS returns #VALUE!.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

sum_range: What to sum (comes FIRST!)
criteria_range1: Where to check first condition
criteria1: First condition
... more pairs as needed (up to 127)

SUMIFS Examples

The pattern never changes: sum_range first, then one criteria_range/criteria pair per condition. To set a lower and an upper bound on the same column, repeat that column as two criteria ranges, as the date-range example does.

Two Conditions

Data:
A          B        C
Product    Region   Sales
Apple      East     100
Orange     West     150
Apple      West     200
Apple      East     120

Sum Apple sales in East region:
=SUMIFS(C:C, A:A, "Apple", B:B, "East")
Result: 220

Multiple Conditions

=SUMIFS(D2:D100, A2:A100, "Widget", B2:B100, ">=50", C2:C100, "California")
Sum sales where:
- Product = "Widget"
- Quantity >= 50
- State = "California"

Date Range

=SUMIFS(C2:C100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&DATE(2026,12,31))
Sum sales in 2026 (DATE() avoids regional date-format surprises)

=SUMIFS(C2:C100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&TODAY())
Sum from start of 2026 to today

For the counting equivalents of every pattern on this page, see SUMIFS and COUNTIFS together and the COUNTIF guide.

Real-World Examples

These formulas use absolute references ($A$2:$A$100) or named ranges (Amounts, Dates) so they can be copied around a report without the ranges drifting.

Sales Report

Total sales by salesperson:
=SUMIF($A$2:$A$100, "John Smith", $C$2:$C$100)

Total sales above quota:
=SUMIF($C$2:$C$100, ">50000")

Sales in Q1:
=SUMIFS($C$2:$C$100, $B$2:$B$100, ">="&DATE(2026,1,1), $B$2:$B$100, "<="&DATE(2026,3,31))

Expense Tracking

Total expenses by category:
=SUMIF(Categories, "Marketing", Amounts)

Expenses over $1000:
=SUMIF(Amounts, ">1000")

Travel expenses in December:
=SUMIFS(Amounts, Categories, "Travel", Dates, ">="&DATE(2026,12,1), Dates, "<="&DATE(2026,12,31))

Inventory Management

Total quantity of product X:
=SUMIF(Products, "Product X", Quantities)

Items below reorder point:
=SUMIF(Stock_Levels, "<"&Reorder_Point, Stock_Levels)

High-value items in warehouse A:
=SUMIFS(Values, Warehouses, "A", Values, ">10000")

Can SUMIF do OR logic?

Not directly — SUMIFS conditions are always AND. For OR logic, add two SUMIFs together, or use SUMPRODUCT when the conditions overlap (adding SUMIFs would double-count rows that match both). SUMIFS with an array constant, wrapped in SUM, is a third option in modern Excel.

Sum "Apple" OR "Orange" sales:
=SUMIF(A:A, "Apple", B:B) + SUMIF(A:A, "Orange", B:B)

Array-constant form (no double counting either):
=SUM(SUMIFS(B:B, A:A, {"Apple","Orange"}))

SUMPRODUCT (needed when conditions can overlap):
=SUMPRODUCT((A2:A10="Apple")+(A2:A10="Orange"), B2:B10)

Common Use Cases

Three patterns worth knowing by heart: a numeric band, an exclusion, and a partial text match.

1. Sum Between Two Values

=SUMIFS(B2:B10, B2:B10, ">=50", B2:B10, "<=100")
Sum values between 50 and 100

2. Sum Excluding Specific Values

=SUMIF(A2:A10, "<>Cancelled", B2:B10)
Sum all except "Cancelled"

3. Sum with Wildcards

=SUMIF(A2:A10, "*Widget*", B2:B10)
Sum any product containing "Widget"

=SUMIF(A2:A10, "A*", B2:B10)
Sum products starting with "A"

Need a literal * or ?  Escape with a tilde: "~*"

Advanced Techniques

Criteria don't have to be typed into the formula. Point them at a cell, build them with &, or calculate them from TODAY() so the total updates without anyone editing the formula.

Dynamic Criteria

Sum based on dropdown selection:
=SUMIF($A$2:$A$100, $E$1, $B$2:$B$100)
Where E1 contains dropdown

Partial Match with SUMIFS

=SUMIFS(C2:C100, A2:A100, "*"&E1&"*")
Sum where column A contains value in E1

Current Month Sales

=SUMIFS(Sales, Dates, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
                Dates, "<"&TODAY())

Rolling 12-Month Sum

=SUMIFS(Sales, Dates, ">="&TODAY()-365, Dates, "<="&TODAY())

Why is my SUMIF returning 0 or #VALUE!?

A 0 usually means one of two things: the criteria never matched (often a cell reference typed inside the quotes), or sum_range holds numbers stored as text, which SUMIF skips. A #VALUE! usually means SUMIFS ranges of different sizes, or a range that points to a closed workbook (open it and press F9). Each case and its fix is below.

Criteria Quoting Mistakes

WRONG: =SUMIF(A2:A10, >50, B2:B10)   ← Excel refuses the formula
RIGHT: =SUMIF(A2:A10, ">50", B2:B10)

WRONG: =SUMIF(A2:A10, ">=C1", B2:B10)   ← compares to the text "C1", not the value in C1
RIGHT: =SUMIF(A2:A10, ">="&C1, B2:B10)

Returns 0: Numbers Stored as Text

SUMIF skips text in sum_range, so numbers imported as text
(left-aligned, often with a green triangle) give 0 or a total that is too low.

Fix the data: select the column, Data > Text to Columns > Finish
Or convert in a helper column: =VALUE(B2)

Or coerce inside the formula (every cell in B2:B10 must be a number
or number-like text):
=SUMPRODUCT(--(A2:A10="Apple"), --B2:B10)

Mismatched or Misaligned Ranges

SUMIFS: every range must be the same size, or you get #VALUE!
WRONG: =SUMIFS(B2:B20, A2:A10, "Apple")
RIGHT: =SUMIFS(B2:B10, A2:A10, "Apple")

SUMIF: no error. Excel resizes sum_range to match range,
starting from its top-left cell, so a shifted start row
silently sums the wrong cells.
WRONG: =SUMIF(A2:A10, "Apple", B3:B11)   ← each match reads one row down
RIGHT: =SUMIF(A2:A10, "Apple", B2:B10)
OK:    =SUMIF(A2:A10, "Apple", B2:B20)   ← treated as B2:B10, correct result

SUMIFS Argument Order

WRONG: =SUMIFS(A2:A10, "Apple", B2:B10)
Order is different from SUMIF!

RIGHT: =SUMIFS(B2:B10, A2:A10, "Apple")
sum_range comes first in SUMIFS

Performance Tips

These habits keep SUMIF formulas readable, easy to copy, and easier to audit as a workbook grows.

• Use specific ranges: A2:A100 instead of A:A
• Lock references with $: $A$2:$A$100
• Use SUMIFS instead of nested IFs
• Consider pivot tables for complex multi-dimension summaries
• Use named ranges for readability

Quick Reference

Both syntaxes side by side, plus the operators and wildcards that work inside a criteria string.

SUMIF:
=SUMIF(range, criteria, sum_range)
=SUMIF(A2:A10, "Apple", B2:B10)
=SUMIF(B2:B10, ">100")

SUMIFS:
=SUMIFS(sum_range, range1, crit1, range2, crit2, ...)
=SUMIFS(C2:C10, A2:A10, "Apple", B2:B10, ">100")

Operators:
">", "<", ">=", "<=", "<>", "="

Wildcards:
"*" = any characters
"?" = one character
"~*" = literal asterisk

Best Practices

Most broken SUMIF and SUMIFS formulas trace back to argument order or criteria quoting, so these rules target both.

• Use SUMIFS for multiple conditions (don't nest SUMIF)
• Remember argument order: SUMIF (range first), SUMIFS (sum_range first)
• Always use quotes and & for criteria with operators
• Build dates with DATE() instead of typing regional formats
• Lock ranges with $ when copying formulas
• Test with small data before applying to full dataset