ASSIGNMENT – 43
ANSWER KEY – USE OF SUMIF & SUMIFS
Sales Data Range
-
Salesman:
B2:B16 -
City:
C2:C16 -
Product:
D2:D16 -
Sales:
E2:E16 -
Status:
G2:G16
Q1. Calculate Total Sales
Formula
=SUM(E2:E16)Answer:
₹5,49,000
Q2. Calculate Total Sales from Sirsa
Formula
=SUMIF(C2:C16,"Sirsa",E2:E16)Answer:
₹2,20,000
Q3. Calculate Total Sales from Hisar
Formula
=SUMIF(C2:C16,"Hisar",E2:E16)Answer:
₹1,67,000
Q4. Calculate Total Sales of Laptop
Formula
=SUMIF(D2:D16,"Laptop",E2:E16)Answer:
₹2,82,000
Q5. Calculate Total Sales of Printer
Formula
=SUMIF(D2:D16,"Printer",E2:E16)Answer:
₹1,31,000
Q6. Calculate Achieved Sales
Formula
=SUMIF(G2:G16,"Achieved",E2:E16)Answer:
₹4,00,000
Q7. Calculate Pending Sales
Formula
=SUMIF(G2:G16,"Pending",E2:E16)Answer:
₹1,49,000
Verification
Achieved + Pending
₹4,00,000 + ₹1,49,000 = ₹5,49,000
This matches the total sales from Q1. ✅
Q8. Calculate Sirsa Laptop Sales
Conditions:
-
City = Sirsa
-
Product = Laptop
Formula
=SUMIFS(E2:E16,C2:C16,"Sirsa",D2:D16,"Laptop")Matching Sales:
| Salesman | Sales |
|---|---|
| Aarav | ₹45,000 |
| Rahul | ₹52,000 |
| Rohan | ₹48,000 |
Answer:
₹1,45,000
Q9. Calculate Hisar Sales Above ₹30,000
Conditions:
-
City = Hisar
-
Sales > ₹30,000
Formula
=SUMIFS(E2:E16,C2:C16,"Hisar",E2:E16,">30000")Matching Sales:
| Salesman | Sales |
|---|---|
| Karan | ₹40,000 |
| Neha | ₹32,000 |
| Nitin | ₹37,000 |
Calculation:
₹40,000 + ₹32,000 + ₹37,000
Answer:
₹1,09,000
Q10. Calculate Achieved Laptop Sales
Conditions:
-
Product = Laptop
-
Status = Achieved
Formula
=SUMIFS(E2:E16,D2:D16,"Laptop",G2:G16,"Achieved")Matching Sales:
| Salesman | Sales |
|---|---|
| Rahul | ₹52,000 |
| Mohit | ₹60,000 |
| Rohan | ₹48,000 |
Calculation:
₹52,000 + ₹60,000 + ₹48,000
Answer:
₹1,60,000
BONUS ANSWERS ⭐
Q11. Calculate Total Sales from Delhi
Formula
=SUMIF(C2:C16,"Delhi",E2:E16)Answer:
₹1,62,000
Q12. Calculate Total Sales Where Sales ≥ ₹40,000
Formula
=SUMIF(E2:E16,">=40000",E2:E16)Matching Sales:
-
₹45,000
-
₹52,000
-
₹40,000
-
₹60,000
-
₹48,000
-
₹42,000
Answer:
₹2,87,000
Q13. Calculate Total Sales from Sirsa Where Status = Achieved
Formula
=SUMIFS(E2:E16,C2:C16,"Sirsa",G2:G16,"Achieved")Matching Sales:
| Salesman | Sales |
|---|---|
| Rahul | ₹52,000 |
| Simran | ₹22,000 |
| Rohan | ₹48,000 |
| Riya | ₹26,000 |
Answer:
₹1,48,000
Q14. Calculate Total Printer Sales from Hisar
Formula
=SUMIFS(E2:E16,C2:C16,"Hisar",D2:D16,"Printer")Matching Sales:
| Salesman | Sales |
|---|---|
| Meera | ₹28,000 |
| Ananya | ₹30,000 |
Answer:
₹58,000
Q15. Delhi + Achieved + Sales ≥ ₹40,000
Conditions:
-
City = Delhi
-
Status = Achieved
-
Sales ≥ ₹40,000
Formula
=SUMIFS(E2:E16,C2:C16,"Delhi",G2:G16,"Achieved",E2:E16,">=40000")Matching Sales:
| Salesman | Sales |
|---|---|
| Mohit | ₹60,000 |
| Vikas | ₹42,000 |
Calculation:
₹60,000 + ₹42,000 = ₹1,02,000
Answer:
₹1,02,000
FINAL ANSWER SUMMARY
| Question | Formula Type | Answer |
|---|---|---|
| Q1 | SUM | ₹5,49,000 |
| Q2 | SUMIF | ₹2,20,000 |
| Q3 | SUMIF | ₹1,67,000 |
| Q4 | SUMIF | ₹2,82,000 |
| Q5 | SUMIF | ₹1,31,000 |
| Q6 | SUMIF | ₹4,00,000 |
| Q7 | SUMIF | ₹1,49,000 |
| Q8 | SUMIFS | ₹1,45,000 |
| Q9 | SUMIFS | ₹1,09,000 |
| Q10 | SUMIFS | ₹1,60,000 |
| Q11 | SUMIF | ₹1,62,000 |
| Q12 | SUMIF | ₹2,87,000 |
| Q13 | SUMIFS | ₹1,48,000 |
| Q14 | SUMIFS | ₹58,000 |
| Q15 | SUMIFS | ₹1,02,000 |
QUICK REVISION
SUMIF → One Condition
=SUMIF(criteria_range,criteria,sum_range)Example:
=SUMIF(C2:C16,"Sirsa",E2:E16)SUMIFS → Multiple Conditions
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Example:
=SUMIFS(E2:E16,C2:C16,"Sirsa",D2:D16,"Laptop")Important Difference
SUMIF = One condition
SUMIFS = Two or more conditions
Also remember: In SUMIFS, the sum range comes first.