SUMIF & SUMIFS function
📊 Excel Functions: SUMIF and SUMIFS
✅ 1. SUMIF Function
🔧 Purpose:
SUMIF is used to sum values based on a single condition.
📌 Syntax: SUMIF(range, criteria, [sum_range])
📋 Parameters:
-
range – The range of cells to evaluate against the criteria.
-
criteria – The condition that determines which cells to sum.
-
sum_range (optional) – The actual cells to sum. If omitted, Excel sums the cells in the
range.
🧾 Example:
🗂️ Data:
| A (Product) | B (Sales) |
|---|---|
| Apple | 100 |
| Banana | 150 |
| Apple | 200 |
🎯 Goal:
Sum the sales of "Apple".
✅ Formula: =SUMIF(A2:A4, "Apple", B2:B4)
📤 Result:
300
🪜 Step-by-Step:
-
Type the function
=SUMIF(. -
Select the range to evaluate (e.g.,
A2:A4). -
Type the condition (e.g.,
"Apple"). -
Select the range to sum (e.g.,
B2:B4). -
Press Enter to get the result.
✅ 2. SUMIFS Function
🔧 Purpose:
SUMIFS is used to sum values based on multiple conditions.
📌 Syntax:
📋 Parameters:
-
sum_range – The range of cells to add.
-
criteria_range1 – The first range to evaluate.
-
criteria1 – The condition for the first range.
-
[criteria_range2, criteria2]... – Additional ranges and criteria (optional).
🧾 Example:
🗂️ Data:
| A (Product) | B (Region) | C (Sales) |
|---|---|---|
| Apple | East | 100 |
| Banana | West | 150 |
| Apple | East | 200 |
| Apple | West | 100 |
🎯 Goal:
Sum the sales of "Apple" in the "East" region.
✅ Formula:=SUMIFS(C2:C5, A2:A5, "Apple", B2:B5, "East")
📤 Result: 300
🪜 Step-by-Step:
-
Type the function
=SUMIFS(. -
Select the sum_range (e.g.,
C2:C5). -
Select the first criteria_range (e.g.,
A2:A5) and set the criteria (e.g.,"Apple"). -
Select the second criteria_range (e.g.,
B2:B5) and set the criteria (e.g.,"East"). -
Press Enter.
📝 Tips & Notes
-
Wildcards: Use
*for any number of characters,?for a single character in text-based criteria. -
Comparison Operators: You can use operators like
">100"or"<200". -
SUMIFSis more powerful thanSUMIFbecause it can handle multiple conditions.