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.