Want Excel to add numbers based on conditions?
Learn how to use SUMIF & SUMIFS in Excel to add values based on one or multiple conditions. This easy guide explains both functions with real-life examples and clear formulas.
What is SUMIF?
SUMIF adds values if a single condition is true.
Syntax:
=SUMIF(range, criteria, [sum_range])
range: The range of cells to check for the conditioncriteria: The condition to matchsum_range: The cells to sum (if different from the range)
Example 1: Add Sales for One Product
You have a list of products and their sales. You want to know total sales for “Apples”.
| Product | Sales |
|---|---|
| Apple | 100 |
| Banana | 80 |
| Apple | 120 |
Formula:
=SUMIF(A2:A4, "Apple", B2:B4)
👉 Result: 220
Example 2: Total Sales Above ₹5000
| Product | Sales |
|---|---|
| A | 4000 |
| B | 5500 |
| C | 7000 |
Formula:
=SUMIF(B2:B4, ">5000")
👉 Result: 5500 + 7000 = 12,500
Note: If sum_range is not provided, Excel adds values directly from range.
What is SUMIFS?
SUMIFS is like an advanced version — it adds values based on multiple conditions.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example 3: Sales by Salesperson & Product
| Salesperson | Product | Sales |
|---|---|---|
| John | Apple | 1000 |
| John | Mango | 800 |
| Priya | Apple | 1200 |
Question: Total sales of Apple by John?
Formula:
=SUMIFS(C2:C4, A2:A4, "John", B2:B4, "Apple")
👉 Result: 1000
Example 4: Sales in Region + Target Product
| Region | Product | Sales |
|---|---|---|
| East | Apple | 1500 |
| West | Mango | 1000 |
| East | Mango | 1800 |
Question: Total Mango sales in East region?
Formula:
=SUMIFS(C2:C4, A2:A4, "East", B2:B4, "Mango")
👉 Result: 1800
Example 5: Sales in a Date Range
| Date | Sales |
|---|---|
| 01-Jan-24 | 5000 |
| 15-Jan-24 | 6500 |
| 05-Feb-24 | 7000 |
Question: Total Jan sales?
Formula:
=SUMIFS(B2:B4, A2:A4, ">=01-Jan-2024", A2:A4, "<=31-Jan-2024")
👉 Result: 11,500
When to Use What?
| Task | Use |
|---|---|
| One condition | SUMIF |
| Multiple conditions | SUMIFS |
| Complex date/product/person filters | SUMIFS |
Final Thoughts
- SUMIF is perfect when you need to filter by one thing (like product name).
- SUMIFS gives more control – like filtering by person, product, and month together.
- Use these to make your reports smarter and save hours of manual work.
What’s Next?
In the next post, we’ll learn about the COUNTIF & COUNTIFS in Excel