ASSIGNMENT – 14

Create Pivot Table Using Data – Separate Fruits and Vegetables

Sales Data

Enter the following data into Excel and use it to complete the questions.

ORDER ID	PRODUCT	CATEGORY	AMOUNT	DATE	COUNTRY
1	Apple	Fruit	₹3,250	05-03-2026	India
2	Carrot	Vegetable	₹4,850	06-03-2026	USA
3	Banana	Fruit	₹6,200	07-03-2026	UK
4	Tomato	Vegetable	₹2,750	08-03-2026	Canada
5	Orange	Fruit	₹4,150	09-03-2026	Germany
6	Potato	Vegetable	₹5,600	10-03-2026	Australia
7	Mango	Fruit	₹7,850	11-03-2026	France
8	Broccoli	Vegetable	₹6,450	12-03-2026	USA
9	Apple	Fruit	₹5,300	13-03-2026	Canada
10	Banana	Fruit	₹3,900	14-03-2026	Germany
11	Carrot	Vegetable	₹4,200	15-03-2026	UK
12	Tomato	Vegetable	₹3,650	16-03-2026	India
13	Orange	Fruit	₹5,750	17-03-2026	USA
14	Potato	Vegetable	₹6,100	18-03-2026	France
15	Mango	Fruit	₹8,250	19-03-2026	Canada
16	Broccoli	Vegetable	₹4,900	20-03-2026	Germany
17	Apple	Fruit	₹6,500	21-03-2026	UK
18	Banana	Fruit	₹5,450	22-03-2026	Australia
19	Carrot	Vegetable	₹3,800	23-03-2026	USA
20	Tomato	Vegetable	₹4,750	24-03-2026	Canada
21	Mango	Fruit	₹7,100	25-03-2026	India
22	Potato	Vegetable	₹5,250	26-03-2026	UK
23	Orange	Fruit	₹4,600	27-03-2026	Germany
24	Broccoli	Vegetable	₹6,800	28-03-2026	France
25	Apple	Fruit	₹5,900	29-03-2026	USA
26	Banana	Fruit	₹4,350	30-03-2026	India
27	Carrot	Vegetable	₹4,650	31-03-2026	Germany
28	Mango	Fruit	₹8,500	01-04-2026	Australia
29	Potato	Vegetable	₹5,750	02-04-2026	USA
30	Orange	Fruit	₹6,300	03-04-2026	Canada

Questions

Q.1

How many Fruit and Vegetable items are there in the list?

Use of: COUNTIF

Find the count separately for:

• Fruit

• Vegetable

Q.2

Calculate the total amount of Apple and Banana.

Use of: SUMIF

Find the total separately for:

• Apple

• Banana

Q.3

How many orders/products are there in the list?

Use of: COUNTA

Q.4

How many Apple and Banana orders were made in Canada and the United Kingdom?

Use of: COUNTIFS

Find the count for:

• Apple + Canada

• Apple + UK

• Banana + Canada

• Banana + UK

Q.5

Calculate the total sales of Apple and Banana in the United States.

Use of: SUMIFS

Find the total separately for:

• Apple + USA

• Banana + USA

Pivot Table Questions

Q.6

Create a Pivot Table showing Category-wise Total Amount.

Pivot Table Setup:

• Rows → Category

• Values → Amount → Sum

Q.7

Create a Pivot Table showing Country-wise Total Amount.

Pivot Table Setup:

• Rows → Country

• Values → Amount → Sum

Q.8

Create a Pivot Table showing Product-wise Total Amount.

Pivot Table Setup:

• Rows → Product

• Values → Amount → Sum

Q.9

Create a Pivot Table showing:

Category in Rows and Country in Columns, with Sum of Amount in Values.

Pivot Table Setup:

• Rows → Category

• Columns → Country

• Values → Amount → Sum

Q.10

Using Pivot Tables, find:

1. Which Category has the highest total sales?

2. Which Country has the highest total sales?

3. Which Product has the highest total sales?

Functions to Practise

Function / Feature	Purpose
COUNTA	Count non-empty cells
COUNTIF	Count records based on one condition
COUNTIFS	Count records based on multiple conditions
SUMIF	Calculate total based on one condition
SUMIFS	Calculate total based on multiple conditions
Pivot Table	Summarize and analyze large datasets

Data Range

The complete data range is:

A1:F31

Important Pivot Table Rule

When creating the Pivot Table, make sure Amount is summarized as:

Sum of Amount

and not Count of Amount.

Add answer formulas and resultsAdd step-by-step PivotTable instructions