Have you ever wanted to add only the sales from a specific region, or sum expenses above a certain amount? Instead of manually filtering your data, you can use Excel’s SUMIF function.
The SUMIF function adds numbers in a range that meet a single condition, making it one of the most useful tools for data analysis and reporting.
🔍 What is the SUMIF Function?
The SUMIF function adds values in a range based on a specified condition.
Syntax:
-
range → The range of cells you want to evaluate.
-
criteria → The condition to test (number, text, expression).
-
sum_range (optional) → The range of cells to add. If omitted, Excel uses the range.
✅ Example 1: Sum Sales Greater Than 500
| A (Product) | B (Sales) |
|---|---|
| Apple | 300 |
| Banana | 600 |
| Cherry | 800 |
Formula:
Result: 1400
✔️ Explanation: Adds only values greater than 500 → 600 + 800.
Changing the criteria value 500→200
=SUMIF(B2:B4, “>200”)
=300+600+800
=1700
✅ Example 2: Sum Sales for a Specific Product
Formula:
Result: 600
✔️ Explanation: Looks in column A for “Banana” and sums corresponding values in column B.
=SUMIF(A2:A4, “Cherry”, B2:B4)
=800
✅ Example 3: Using Cell Reference in Criteria
Suppose cell D1 contains the word Apple.
Formula:
Result: 300
✔️ Explanation: Criteria can reference another cell, making formulas dynamic.
=SUMIF(A2:A4, D1, B2:B4)
And…
D1=banana
Result: 600
✅ Example 4: Using Wildcards
-
*→ Matches any number of characters. -
?→ Matches a single character.
Formula:
Result: 600
✔️ Explanation: Sums all sales where product names begin with “B” (Banana).
=SUMIF(A2:A4, “C*”, B2:B4)
Result: 800
Explanation: Sums all sales where product names begin with “C” (Cherry).
✅ Example 5: Sum Negative Numbers (Expenses)
| A (Category) | B (Amount) |
|---|---|
| Rent | -800 |
| Utilities | -200 |
| Sales | 1000 |
Formula:
Result: -1000
✔️ Explanation: Adds only negative values → (-800) + (-200).
🎯 Practical Uses of SUMIF
-
Sales Reports → Add totals for specific regions, products, or salespeople.
-
Budget Tracking → Sum only expenses above/below a threshold.
-
Data Filtering → Add values that match keywords or partial names.
-
Error Handling → Exclude negative or zero values from totals.
📝 Conclusion
The SUMIF function is a must-know Excel tool for anyone working with data. By applying conditions, you can quickly filter and add values without complex formulas.
👉 Once you master SUMIF, try SUMIFS for handling multiple conditions.
