ASSIGNMENT – 39
ANSWER KEY – COUNTA, VLOOKUP, COUNTIF, COUNTIFS & IF
PART A – SALES CALCULATION
Complete Sales Calculation
Formula for Sales:
=SUM(B2:G2)| Salesman | Jan | Feb | Mar | Apr | May | June | Sales | Target | Result | Commission |
|---|---|---|---|---|---|---|---|---|---|---|
| Amit | 2,500 | 1,800 | 1,200 | 2,200 | 1,500 | 1,800 | 11,000 | 10,000 | Achieved | 550 |
| Neha | 4,200 | 1,600 | 900 | 1,800 | 1,400 | 2,500 | 12,400 | 12,000 | Achieved | 1,240 |
| Rahul | 1,800 | 2,200 | 1,600 | 2,800 | 1,900 | 3,000 | 11,300 | 15,000 | Not Achieved | 565 |
| Pooja | 3,200 | 1,400 | 2,100 | 3,500 | 1,800 | 2,500 | 14,500 | 12,000 | Achieved | 1,450 |
| Karan | 900 | 1,200 | 1,800 | 1,600 | 1,300 | 1,500 | 8,300 | 10,000 | Not Achieved | 415 |
| Simran | 2,800 | 2,600 | 1,700 | 2,400 | 2,200 | 3,000 | 14,700 | 14,000 | Achieved | 1,470 |
| Arjun | 1,500 | 1,900 | 1,300 | 2,000 | 1,600 | 1,800 | 10,100 | 10,000 | Achieved | 505 |
| Neeraj | 3,500 | 2,800 | 1,900 | 3,000 | 2,500 | 3,200 | 16,900 | 16,000 | Achieved | 1,690 |
| Priya | 2,200 | 1,700 | 1,100 | 2,300 | 1,800 | 2,100 | 11,200 | 11,000 | Achieved | 560 |
| Mohit | 3,000 | 2,500 | 2,000 | 2,700 | 2,300 | 2,900 | 15,400 | 15,000 | Achieved | 1,540 |
Result Formula
=IF(H2>=I2,"Achieved","Not Achieved")Commission Formula
=IF(H2>=I2,H2*10%,H2*5%)Q1. How Many Salesmen Are in the List?
Formula
=COUNTA(A2:A11)Answer: 10 Salesmen
Q2. Find Sales, Target and Result of Simran
Using VLOOKUP:
Sales
=VLOOKUP("Simran",$A$2:$J$11,8,FALSE)Answer: ₹14,700
Target
=VLOOKUP("Simran",$A$2:$J$11,9,FALSE)Answer: ₹14,000
Result
=VLOOKUP("Simran",$A$2:$J$11,10,FALSE)Answer: Achieved
Final Answer:
Simran → Sales ₹14,700 | Target ₹14,000 | Achieved
Q3. How Many Salesmen Achieved Their Target?
Formula
=COUNTIF(J2:J11,"Achieved")Answer: 8 Salesmen
Achieved:
-
Amit
-
Neha
-
Pooja
-
Simran
-
Arjun
-
Neeraj
-
Priya
-
Mohit
Q4. How Many Salesmen Did NOT Achieve Their Target?
Formula
=COUNTIF(J2:J11,"Not Achieved")Answer:
2 Salesmen
They are:
-
Rahul
-
Karan
Q5. January Sales > 2,000 AND March Sales <= 1,500
Formula
=COUNTIFS(B2:B11,">2000",D2:D11,"<=1500")Matching Salesmen:
| Salesman | January | March |
|---|---|---|
| Amit | 2,500 | 1,200 |
| Neha | 4,200 | 900 |
| Priya | 2,200 | 1,100 |
Answer: 3 Salesmen
Q6. February Sales > 2,000 AND June Sales > 2,500
Formula
=COUNTIFS(C2:C11,">2000",G2:G11,">2500")Matching Salesmen:
| Salesman | February | June |
|---|---|---|
| Rahul | 2,200 | 3,000 |
| Simran | 2,600 | 3,000 |
| Neeraj | 2,800 | 3,200 |
| Mohit | 2,500 | 2,900 |
Answer: 4 Salesmen
Q7. Conditional Formatting – January Sales
Condition:
-
January Sales >= 2,000
-
January Sales <= 3,000
-
Font = Red
-
Background = Yellow
Formula
=AND(B2>=2000,B2<=3000)Matching values:
-
Amit → 2,500
-
Rahul → 1,800 ❌
-
Pooja → 3,200 ❌
-
Simran → 2,800
-
Priya → 2,200
-
Mohit → 3,000
Therefore, the values to highlight are:
2,500, 2,800, 2,200 and 3,000
Q8. Calculate Commission
Formula
=IF(H2>=I2,H2*10%,H2*5%)| Salesman | Sales | Target | Result | Commission |
|---|---|---|---|---|
| Amit | 11,000 | 10,000 | Achieved | 550 |
| Neha | 12,400 | 12,000 | Achieved | 1,240 |
| Rahul | 11,300 | 15,000 | Not Achieved | 565 |
| Pooja | 14,500 | 12,000 | Achieved | 1,450 |
| Karan | 8,300 | 10,000 | Not Achieved | 415 |
| Simran | 14,700 | 14,000 | Achieved | 1,470 |
| Arjun | 10,100 | 10,000 | Achieved | 505 |
| Neeraj | 16,900 | 16,000 | Achieved | 1,690 |
| Priya | 11,200 | 11,000 | Achieved | 560 |
| Mohit | 15,400 | 15,000 | Achieved | 1,540 |
Q9. Find Target and Result Using VLOOKUP
Rahul
Target
=VLOOKUP("Rahul",$A$2:$J$11,9,FALSE)₹15,000
Result
=VLOOKUP("Rahul",$A$2:$J$11,10,FALSE)Not Achieved
Pooja
Target: ₹12,000
Result: Achieved
Neha
Target: ₹12,000
Result: Achieved
Final Answer
| Salesman | Target | Result |
|---|---|---|
| Rahul | 15,000 | Not Achieved |
| Pooja | 12,000 | Achieved |
| Neha | 12,000 | Achieved |
Q10. January > 2,000 AND February > 1,500 AND March < 2,000
Formula
=COUNTIFS(B2:B11,">2000",C2:C11,">1500",D2:D11,"<2000")Matching Salesmen:
| Salesman | Jan | Feb | Mar |
|---|---|---|---|
| Amit | 2,500 | 1,800 | 1,200 |
| Neha | 4,200 | 1,600 | 900 |
| Simran | 2,800 | 2,600 | 1,700 |
| Priya | 2,200 | 1,700 | 1,100 |
Answer: 4 Salesmen
BONUS ANSWERS
Q11. Highest Total Sales
Highest Sales = ₹16,900
Salesman: Neeraj
Q12. Lowest Total Sales
Lowest Sales = ₹8,300
Salesman: Karan
Q13. Average Total Sales
Formula
=AVERAGE(H2:H11)Total Sales = ₹125,800
Number of Salesmen = 10
Answer:
₹12,580
Q14. January Sales Greater Than 3,000
Formula
=COUNTIF(B2:B11,">3000")Matching:
-
Neha → 4,200
-
Pooja → 3,200
-
Neeraj → 3,500
Answer:
3 Salesmen
Q15. Achieved Target AND June Sales > 2,500
Formula
=COUNTIFS(J2:J11,"Achieved",G2:G11,">2500")Matching Salesmen:
-
Simran
-
Neeraj
-
Mohit
Answer:
3 Salesmen
FINAL ANSWER SUMMARY
| Question | Answer |
|---|---|
| Q1 | 10 Salesmen |
| Q2 | Simran → ₹14,700, ₹14,000, Achieved |
| Q3 | 8 Achieved |
| Q4 | 2 Not Achieved |
| Q5 | 3 Salesmen |
| Q6 | 4 Salesmen |
| Q7 | 4 values highlighted |
| Q8 | Commission calculated using IF |
| Q9 | Rahul → ₹15,000 / Not Achieved; Pooja → ₹12,000 / Achieved; Neha → ₹12,000 / Achieved |
| Q10 | 4 Salesmen |
| Q11 | Neeraj – ₹16,900 |
| Q12 | Karan – ₹8,300 |
| Q13 | ₹12,580 |
| Q14 | 3 Salesmen |
| Q15 | 3 Salesmen |
Important Functions Used
=COUNTA(A2:A11)=VLOOKUP("Simran",$A$2:$J$11,8,FALSE)=COUNTIF(J2:J11,"Achieved")=COUNTIFS(B2:B11,">2000",D2:D11,"<=1500")=IF(H2>=I2,H2*10%,H2*5%)These five formulas cover the main concepts of Assignment 39.