SUM, SUMIF & SUMIFS
Add numbers with or without conditions
SUM, SUMIF & SUMIFS
Add numbers — all of them or only the ones you want!
SUM
Adds all numbers in a range.
=SUM(range)
| A |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
=SUM(A1:A5) → 150
SUMIF
Adds numbers that match one condition.
=SUMIF(range, criteria, sum_range)
Example: Sum sales for "North" region
| A (Region) | B (Sales) |
|---|---|
| North | 100 |
| South | 200 |
| North | 150 |
| South | 300 |
| North | 50 |
=SUMIF(A1:A5, "North", B1:B5) → 300
Adds 100 + 150 + 50 = 300 (only North sales)
SUMIF with Numbers
Sum values greater than 100:
=SUMIF(B1:B5, ">100", B1:B5)
Or shorter: =SUMIF(B1:B5, ">100")
| B (Sales) |
|---|
| 50 |
| 150 |
| 80 |
| 200 |
| 120 |
Result → 470 (150 + 200 + 120)
SUMIFS
Adds numbers that match multiple conditions.
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)
Example: Sum North sales > 100
| A (Region) | B (Sales) |
|---|---|
| North | 150 |
| South | 200 |
| North | 80 |
| North | 120 |
| South | 90 |
=SUMIFS(B1:B5, A1:A5, "North", B1:B5, ">100") → 270
Adds 150 + 120 = 270 (North AND > 100)
Real Life Examples
Total sales:
=SUM(B2:B100)
Total for product "Laptop":
=SUMIF(A2:A100, "Laptop", B2:B100)
Total for Laptop with Qty > 5:
=SUMIFS(C2:C100, A2:A100, "Laptop", B2:B100, ">5")
Quick Comparison
| Function | What It Does | Conditions |
|---|---|---|
| SUM | Adds all numbers | None |
| SUMIF | Adds with 1 rule | One |
| SUMIFS | Adds with many rules | Multiple |
Summary
- SUM → add all numbers
- SUMIF → add with 1 condition
- SUMIFS → add with 2+ conditions
- SUMIFS puts sum_range FIRST
- Put criteria in quotes:
"North",">100"