ASSIGNMENT – 12
Answer Key – SUM, COUNTA, COUNTIF, SUMIF & VLOOKUP
Monthly Fruit Sales Report
|
PRODUCT NAME |
JAN |
FEB |
MAR |
APR |
MAY |
JUN |
JUL |
AUG |
TOTAL SALES |
|---|---|---|---|---|---|---|---|---|---|
|
Apples |
₹8,500 |
₹12,750 |
₹6,250 |
₹9,800 |
₹15,600 |
₹18,200 |
₹21,450 |
₹17,300 |
₹1,09,850 |
|
Bananas |
₹11,200 |
₹9,450 |
₹14,800 |
₹12,600 |
₹16,250 |
₹13,500 |
₹19,750 |
₹15,900 |
₹1,13,450 |
|
Grapes |
₹7,800 |
₹13,200 |
₹10,500 |
₹18,600 |
₹12,400 |
₹16,750 |
₹22,300 |
₹19,200 |
₹1,20,750 |
|
Mangoes |
₹15,500 |
₹18,750 |
₹21,200 |
₹17,400 |
₹25,600 |
₹28,500 |
₹30,200 |
₹26,800 |
₹1,83,950 |
|
Oranges |
₹6,750 |
₹8,900 |
₹12,300 |
₹14,500 |
₹10,800 |
₹13,250 |
₹16,400 |
₹11,750 |
₹94,650 |
|
Peaches |
₹9,600 |
₹11,500 |
₹7,850 |
₹13,200 |
₹14,750 |
₹12,600 |
₹18,300 |
₹15,450 |
₹1,03,250 |
|
Pears |
₹10,800 |
₹14,250 |
₹9,600 |
₹16,400 |
₹13,750 |
₹15,900 |
₹20,500 |
₹18,250 |
₹1,19,450 |
|
Pineapples |
₹13,400 |
₹16,800 |
₹12,750 |
₹19,300 |
₹17,600 |
₹21,500 |
₹24,200 |
₹20,750 |
₹1,46,300 |
|
Papaya |
₹8,250 |
₹10,600 |
₹11,900 |
₹13,750 |
₹12,300 |
₹14,800 |
₹17,600 |
₹16,250 |
₹1,05,450 |
|
Watermelon |
₹12,500 |
₹15,700 |
₹18,900 |
₹20,500 |
₹22,750 |
₹25,600 |
₹29,300 |
₹27,800 |
₹1,73,050 |
Q.1 Count the Total Number of Fruits
=COUNTA(A2:A11)
Answer: 10 Fruits
Q.2 Calculate the Total Sales of Every Fruit
Enter the formula in cell J2:
=SUM(B2:I2)
Answer:
-
Apples → ₹1,09,850
-
Bananas → ₹1,13,450
-
Grapes → ₹1,20,750
-
Mangoes → ₹1,83,950
-
Oranges → ₹94,650
-
Peaches → ₹1,03,250
-
Pears → ₹1,19,450
-
Pineapples → ₹1,46,300
-
Papaya → ₹1,05,450
-
Watermelon → ₹1,73,050
Note: Drag the formula down from J2 to J11.
Q.3 Find the Total Sales of Mangoes Using VLOOKUP
=VLOOKUP("Mangoes",A2:J11,10,FALSE)
Answer: ₹1,83,950
Q.4 Find the Total Sales of Pineapples Using VLOOKUP
=VLOOKUP("Pineapples",A2:J11,10,FALSE)
Answer: ₹1,46,300
Q.5 Find the March Sales of Grapes Using VLOOKUP
=VLOOKUP("Grapes",A2:I11,4,FALSE)
Answer: ₹10,500
Q.6 Find the July Sales of Watermelon Using VLOOKUP
=VLOOKUP("Watermelon",A2:I11,8,FALSE)
Answer: ₹29,300
Q.7 Count Fruits Whose January Sales Are Greater Than ₹10,000
=COUNTIF(B2:B11,">10000")
Answer: 5 Fruits
Q.8 Calculate Total Sales of Fruits Whose Total Sales Are Greater Than ₹1,00,000
=SUMIF(J2:J11,">100000",J2:J11)
Answer: ₹11,75,500
Q.9 Find the May and August Sales of Apples Using VLOOKUP
May Sales
=VLOOKUP("Apples",A2:I11,6,FALSE)
Answer: ₹15,600
August Sales
=VLOOKUP("Apples",A2:I11,9,FALSE)
Answer: ₹17,300
Q.10 Calculate the Grand Total Sales of All Fruits
=SUM(B2:I11)
Answer: ₹10,69,150
Completed Answer Summary
|
Q. No. |
Final Answer |
|---|---|
|
Q1 |
10 Fruits |
|
Q2 |
Total sales of every fruit calculated |
|
Q3 |
Mangoes → ₹1,83,950 |
|
Q4 |
Pineapples → ₹1,46,300 |
|
Q5 |
Grapes March Sales → ₹10,500 |
|
Q6 |
Watermelon July Sales → ₹29,300 |
|
Q7 |
5 Fruits |
|
Q8 |
₹11,75,500 |
|
Q9 |
Apples May → ₹15,600; August → ₹17,300 |
|
Q10 |
₹10,69,150 |
Key Points
-
SUM → Adds numbers.
-
COUNTA → Counts non-empty cells.
-
COUNTIF → Counts cells that meet a condition.
-
SUMIF → Adds values that meet a condition.
-
VLOOKUP → Searches vertically in the first column and returns a value from the specified column.
-
FALSE → Finds an exact match.