What are SUMIF and SUMIFS?

SUMIF: Sum cells that meet one condition

SUMIFS: Sum cells that meet multiple conditions (AND logic)

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)

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

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, ">=1/1/2024", A2:A100, "<=12/31/2024")
Sum sales in 2024

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

SUMIF vs SUMIFS Key Differences

Aspect	SUMIF	SUMIFS
Conditions	One condition	Multiple (AND logic)
Argument order	range, criteria, sum_range	sum_range, range1, crit1, ...
sum_range	Optional (3rd argument)	Required (1st argument)
Use when	Single condition	Multiple conditions

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, ">=1/1/2024", $B$2:$B$100, "<=3/31/2024")

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, ">=12/1/2024", Dates, "<=12/31/2024")

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

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 with OR Logic

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

Or use SUMPRODUCT:
=SUMPRODUCT((A2:A10="Apple")+(A2:A10="Orange"), B2:B10)

3. Sum Excluding Specific Values

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

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

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

Common Errors

#VALUE! Error

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

WRONG: =SUMIF(A2:A10, >=C1, B2:B10)
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 summaries
• Use named ranges for readability

Alternative: SUMPRODUCT

More flexible than SUMIF/SUMIFS:

=SUMPRODUCT((A2:A10="Apple")*(B2:B10>100)*C2:C10)
Sum where product = "Apple" AND price > 100

=SUMPRODUCT((A2:A10="Apple")+(A2:A10="Orange"), C2:C10)
Sum where product = "Apple" OR "Orange" (OR logic!)

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

Common Formulas

Sum by category:        =SUMIF(Categories, "Food", Amounts)
Sum > value:            =SUMIF(Amounts, ">1000")
Sum this year:          =SUMIFS(Sales, Dates, ">="&DATE(YEAR(TODAY()),1,1))
Sum last month:         =SUMIFS(Sales, Dates, ">="&DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1),
                                      Dates, "<"&DATE(YEAR(TODAY()), MONTH(TODAY()), 1))
Sum with 2 conditions:  =SUMIFS(Sales, Products, "Widget", Regions, "East")
Sum excluding:          =SUMIF(Status, "<>Cancelled", Amounts)

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
• Lock ranges with $ when copying formulas
• Use named ranges for better readability
• Test with small data before applying to full dataset