SUMIF in Excel: Criteria Syntax, Examples, and SUMIFS

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)   → #VALUE! or nonsense

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

SUMIF Syntax

=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

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

SUMIFS Syntax

=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

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

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

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

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!?

Three culprits cover almost every broken SUMIF: operators outside quotes (or cell references inside them), mismatched range sizes between the test range and sum range, and numbers stored as text so "50" never equals 50. Check those in order and you'll find the bug.

#VALUE! Error

WRONG: =SUMIF(A2:A10, >50, B2:B10)
RIGHT: =SUMIF(A2:A10, ">50", B2:B10)

WRONG: =SUMIF(A2:A10, ">=C1", B2:B10)   ← looks for the text ">=C1"
RIGHT: =SUMIF(A2:A10, ">="&C1, B2:B10)

Wrong Range Sizes

WRONG: =SUMIF(A2:A10, "Apple", B2:B20)
Ranges must be same size!

RIGHT: =SUMIF(A2:A10, "Apple", B2:B10)

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

• 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

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

• 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