ASSIGNMENT – 6
Use of COUNTA, COUNTIF, SUMIF, HLOOKUP & Conditional Formatting
Data Table
| Sr. No. | Items | Date | Cost |
|---|---|---|---|
| 1 | BATTERY | 05-01-2026 | ₹900 |
| 2 | TYRES | 12-01-2026 | ₹2,200 |
| 3 | BRAKES | 18-02-2026 | ₹600 |
| 4 | SERVICE | 25-02-2026 | ₹1,200 |
| 5 | WINDOW | 10-03-2026 | ₹1,500 |
| 6 | CLUTCH | 15-03-2026 | ₹1,800 |
| 7 | TYRES | 20-03-2026 | ₹2,500 |
| 8 | BRAKES | 05-04-2026 | ₹1,100 |
| 9 | WINDOW | 12-04-2026 | ₹1,400 |
| 10 | SERVICE | 18-04-2026 | ₹900 |
| 11 | CLUTCH | 25-04-2026 | ₹2,100 |
| 12 | TYRES | 05-05-2026 | ₹1,600 |
| 13 | WINDOW | 10-05-2026 | ₹1,900 |
| 14 | BRAKES | 15-05-2026 | ₹700 |
| 15 | SERVICE | 20-06-2026 | ₹1,300 |
| 16 | CLUTCH | 25-06-2026 | ₹1,700 |
| 17 | TYRES | 05-07-2026 | ₹2,300 |
| 18 | WINDOW | 12-07-2026 | ₹1,800 |
| 19 | BRAKES | 20-07-2026 | ₹1,000 |
| 20 | SERVICE | 25-08-2026 | ₹1,500 |
Questions
Q.1 COUNTA Formula
How many items are there in the list?
Use the COUNTA formula.
Q.2 COUNTIF Formula – Item-wise Count
Using COUNTIF, calculate how many times the following items have been purchased:
-
BRAKES
-
WINDOW
-
TYRES
Q.3 COUNTIF Formula – Cost Analysis
Using COUNTIF, calculate:
-
How many items have a Cost greater than ₹1,500?
-
How many items have a Cost less than ₹1,500?
Q.4 Conditional Formatting – TYRES
Using Conditional Formatting, highlight all TYRES items in the Items column.
Q.5 Conditional Formatting – Cost Range
Using Conditional Formatting, highlight all costs between ₹1,000 and ₹2,000.
Q.6 HLOOKUP Formula
Arrange the data horizontally and use the HLOOKUP formula to find the Item Name at the following positions:
-
Position 15
-
Position 18
-
Position 20
Q.7 SUMIF Formula – WINDOW
Using SUMIF, calculate the total Cost of all WINDOW items.
Q.8 SUMIF Formula – BRAKES
Using SUMIF, calculate the total Cost of all BRAKES items.
Q.9 SUMIF Formula – TYRES
Using SUMIF, calculate the total Cost of all TYRES items.
Q.10 COUNTIF Formula – Cost Conditions
Using COUNTIF, calculate:
-
How many items have a Cost equal to ₹1,500?
-
How many items have a Cost greater than or equal to ₹2,000?
Formulas to Practice
COUNTA | COUNTIF | SUMIF | HLOOKUP | Conditional Formatting
HLOOKUP Practice
For Q.6, first arrange the data horizontally so that the position numbers can be used with HLOOKUP.
Example structure:
| Position | 1 | 2 | 3 | 4 | … |
|---|---|---|---|---|---|
| Item | BATTERY | TYRES | BRAKES | SERVICE | … |
Then use HLOOKUP to retrieve the required item.