ASSIGNMENT – 14

Answer Key – Pivot Table: Fruits and Vegetables

Data Range

• Product: B2:B31

• Category: C2:C31

• Amount: D2:D31

• Country: F2:F31

Q.1 How many Fruit and Vegetable items are there?

Fruit

=COUNTIF(C2:C31,"Fruit")

Answer: 16 Fruits

Vegetable

=COUNTIF(C2:C31,"Vegetable")

Answer: 14 Vegetables

Category	Number of Orders
Fruit	16
Vegetable	14
Total	30

Q.2 Total Amount of Apple and Banana

Apple

=SUMIF(B2:B31,"Apple",D2:D31)

Answer: ₹20,950

Calculation:

₹3,250 + ₹5,300 + ₹6,500 + ₹5,900 = ₹20,950

Banana

=SUMIF(B2:B31,"Banana",D2:D31)

Answer: ₹19,900

Calculation:

₹6,200 + ₹3,900 + ₹5,450 + ₹4,350 = ₹19,900

Product	Total Amount
Apple	₹20,950
Banana	₹19,900
Total	₹40,850

Q.3 How many orders/products are there?

=COUNTA(A2:A31)

Answer: 30 Orders

Q.4 Apple and Banana orders in Canada and UK

Apple + Canada

=COUNTIFS(B2:B31,"Apple",F2:F31,"Canada")

Answer: 1

Apple + UK

=COUNTIFS(B2:B31,"Apple",F2:F31,"UK")

Answer: 1

Banana + Canada

=COUNTIFS(B2:B31,"Banana",F2:F31,"Canada")

Answer: 0

Banana + UK

=COUNTIFS(B2:B31,"Banana",F2:F31,"UK")

Answer: 1

Product	Canada	UK
Apple	1	1
Banana	0	1

Total Apple + Banana orders in Canada and UK = 3

Q.5 Total Sales of Apple and Banana in USA

Apple + USA

=SUMIFS(D2:D31,B2:B31,"Apple",F2:F31,"USA")

Answer: ₹5,900

Banana + USA

=SUMIFS(D2:D31,B2:B31,"Banana",F2:F31,"USA")

Answer: ₹0

Combined Total

₹5,900

Product	USA Sales
Apple	₹5,900
Banana	₹0
Total	₹5,900

Q.6 Pivot Table – Category-wise Total Amount

Pivot Table Setup:

• Rows → Category

• Values → Sum of Amount

Category	Total Amount
Fruit	₹93,350
Vegetable	₹69,500
Grand Total	₹1,62,850

Answer:

Fruit has the higher total sales: ₹93,350

Q.7 Pivot Table – Country-wise Total Amount

Country	Total Amount
India	₹18,350
USA	₹32,500
UK	₹22,150
Canada	₹27,350
Germany	₹22,200
Australia	₹19,550
France	₹20,750
Grand Total	₹1,62,850

Answer:

USA has the highest total sales: ₹32,500

Q.8 Pivot Table – Product-wise Total Amount

Product	Total Amount
Apple	₹20,950
Banana	₹19,900
Orange	₹20,800
Mango	₹31,700
Carrot	₹17,500
Tomato	₹11,150
Potato	₹22,700
Broccoli	₹18,150
Grand Total	₹1,62,850

Answer:

Mango has the highest total sales: ₹31,700

Q.9 Category in Rows and Country in Columns

Pivot Table Setup:

• Rows → Category

• Columns → Country

• Values → Sum of Amount

Category	India	USA	UK	Canada	Germany	Australia	France	Grand Total
Fruit	₹14,700	₹11,650	₹12,700	₹19,850	₹12,650	₹13,950	₹7,850	₹93,350
Vegetable	₹3,650	₹20,850	₹9,450	₹7,500	₹9,550	₹5,600	₹12,900	₹69,500
Grand Total	₹18,350	₹32,500	₹22,150	₹27,350	₹22,200	₹19,550	₹20,750	₹1,62,850

Q.10 Find the Highest Total Sales

1. Which Category has the highest total sales?

Category	Total Sales
Fruit	₹93,350
Vegetable	₹69,500

Answer: Fruit – ₹93,350

2. Which Country has the highest total sales?

Country	Total Sales
USA	₹32,500
Canada	₹27,350
UK	₹22,150
Germany	₹22,200
France	₹20,750
Australia	₹19,550
India	₹18,350

Answer: USA – ₹32,500

3. Which Product has the highest total sales?

Product	Total Sales
Mango	₹31,700
Potato	₹22,700
Apple	₹20,950
Orange	₹20,800
Banana	₹19,900
Broccoli	₹18,150
Carrot	₹17,500
Tomato	₹11,150

Answer: Mango – ₹31,700

Final Answer Summary

Q. No.	Answer
Q1	Fruit = 16, Vegetable = 14
Q2	Apple = ₹20,950, Banana = ₹19,900
Q3	30 Orders
Q4	Apple–Canada = 1, Apple–UK = 1, Banana–Canada = 0, Banana–UK = 1
Q5	Apple–USA = ₹5,900, Banana–USA = ₹0
Q6	Fruit = ₹93,350, Vegetable = ₹69,500
Q7	USA = ₹32,500 highest
Q8	Mango = ₹31,700 highest
Q9	Category × Country Pivot Table – see above
Q10	Highest Category = Fruit, Country = USA, Product = Mango
Grand Total	₹1,62,850