ASSIGNMENT – 6 – ANSWER KEY

COUNTA, COUNTIF, SUMIF, HLOOKUP & Conditional Formatting

Completed Data Summary

Sr. No. Items Cost
1 BATTERY ₹900
2 TYRES ₹2,200
3 BRAKES ₹600
4 SERVICE ₹1,200
5 WINDOW ₹1,500
6 CLUTCH ₹1,800
7 TYRES ₹2,500
8 BRAKES ₹1,100
9 WINDOW ₹1,400
10 SERVICE ₹900
11 CLUTCH ₹2,100
12 TYRES ₹1,600
13 WINDOW ₹1,900
14 BRAKES ₹700
15 SERVICE ₹1,300
16 CLUTCH ₹1,700
17 TYRES ₹2,300
18 WINDOW ₹1,800
19 BRAKES ₹1,000
20 SERVICE ₹1,500

Q.1 COUNTA – Total Items

Assuming Items are in B2:

=COUNTA(B2:B21)

Answer: 20 Items

Q.2 COUNTIF – Item-wise Count

BRAKES

=COUNTIF(B2:B21,"BRAKES")

Answer: 4

WINDOW

=COUNTIF(B2:B21,"WINDOW")

Answer: 4

TYRES

=COUNTIF(B2:B21,"TYRES")

Answer: 4

Final Answer

Item Count
BRAKES 4
WINDOW 4
TYRES 4

Q.3 COUNTIF – Cost Analysis

Assuming Cost is in D2.

Cost greater than ₹1,500

=COUNTIF(D2:D21,">1500")

Answer: 8

Cost less than ₹1,500

=COUNTIF(D2:D21,"<1500")

Answer: 8

Q.4 Conditional Formatting – TYRES

To highlight all TYRES:

  1. Select the Items range B2.

  2. Go to Home → Conditional Formatting.

  3. Select Highlight Cells Rules → Text that Contains.

  4. Enter:

TYRES

  1. Select a formatting style.

  2. Click OK.

Result:

All 4 TYRES records will be highlighted.

Q.5 Conditional Formatting – Cost ₹1,000 to ₹2,000

  1. Select D2.

  2. Go to Home → Conditional Formatting.

  3. Select Highlight Cells Rules → Between.

  4. Enter:

    • 1000

    • 2000

  5. Select a formatting style.

  6. Click OK.

Result:

All costs from ₹1,000 to ₹2,000, including both limits, will be highlighted.

Q.6 HLOOKUP – Position 15, 18 & 20

After arranging the data horizontally:

Position 1 2 3 4 5 … 15 … 18 … 20
Item BATTERY TYRES BRAKES SERVICE WINDOW … SERVICE … WINDOW … SERVICE

Position 15

=HLOOKUP(15,B1:U2,2,FALSE)

Answer: SERVICE

Position 18

=HLOOKUP(18,B1:U2,2,FALSE)

Answer: WINDOW

Position 20

=HLOOKUP(20,B1:U2,2,FALSE)

Answer: SERVICE

Q.7 SUMIF – Total Cost of WINDOW

Formula:

=SUMIF(B2:B21,"WINDOW",D2:D21)

Calculation:

₹1,500 + ₹1,400 + ₹1,900 + ₹1,800

Answer:

₹6,600

Q.8 SUMIF – Total Cost of BRAKES

Formula:

=SUMIF(B2:B21,"BRAKES",D2:D21)

Calculation:

₹600 + ₹1,100 + ₹700 + ₹1,000

Answer:

₹3,400

Q.9 SUMIF – Total Cost of TYRES

Formula:

=SUMIF(B2:B21,"TYRES",D2:D21)

Calculation:

₹2,200 + ₹2,500 + ₹1,600 + ₹2,300

Answer:

₹8,600

Q.10 COUNTIF – Cost Conditions

1. Cost equal to ₹1,500

Formula:

=COUNTIF(D2:D21,1500)

Answer: 2

The ₹1,500 costs occur at positions 5 and 20.

2. Cost greater than or equal to ₹2,000

Formula:

=COUNTIF(D2:D21,">=2000")

Answer: 4

The qualifying costs are:

  • ₹2,200

  • ₹2,500

  • ₹2,100

  • ₹2,300

Final Answers

Question Answer
Q.1 Total Items 20
Q.2 BRAKES 4
Q.2 WINDOW 4
Q.2 TYRES 4
Q.3 Cost > ₹1,500 8
Q.3 Cost < ₹1,500 8
Q.6 Position 15 SERVICE
Q.6 Position 18 WINDOW
Q.6 Position 20 SERVICE
Q.7 Total WINDOW Cost ₹6,600
Q.8 Total BRAKES Cost ₹3,400
Q.9 Total TYRES Cost ₹8,600
Q.10 Cost = ₹1,500 2
Q.10 Cost ≥ ₹2,000 4
PreviousNext