PRODUCT, IF, COUNTA, COUNTIF & SUMIF
Data Table with Answers
| Sr. No. | Items | Qty | Rate | Amount | Grade |
|---|---|---|---|---|---|
| 1 | LAPTOP | 12 | 55,000 | ₹6,60,000 | Expensive |
| 2 | COMPUTER | 15 | 32,000 | ₹4,80,000 | Let’s Buy It |
| 3 | PRINTER | 20 | 8,500 | ₹1,70,000 | Let’s Buy It |
| 4 | MONITOR | 18 | 12,000 | ₹2,16,000 | Let’s Buy It |
| 5 | KEYBOARD | 35 | 800 | ₹28,000 | Let’s Buy It |
| 6 | MOUSE | 25 | 450 | ₹11,250 | Let’s Buy It |
| 7 | COMPUTER | 10 | 30,000 | ₹3,00,000 | Let’s Buy It |
| 8 | UPS | 16 | 6,500 | ₹1,04,000 | Let’s Buy It |
| 9 | SCANNER | 8 | 15,000 | ₹1,20,000 | Let’s Buy It |
| 10 | COMPUTER | 22 | 28,000 | ₹6,16,000 | Expensive |
Q.1 PRODUCT Formula – Calculate Amount
Assuming:
-
C2 = Qty
-
D2 = Rate
-
E2 = Amount
Enter this formula in E2:
=PRODUCT(C2,D2)Copy the formula down to E11.
Answers
| Item | Amount |
|---|---|
| LAPTOP | ₹6,60,000 |
| COMPUTER | ₹4,80,000 |
| PRINTER | ₹1,70,000 |
| MONITOR | ₹2,16,000 |
| KEYBOARD | ₹28,000 |
| MOUSE | ₹11,250 |
| COMPUTER | ₹3,00,000 |
| UPS | ₹1,04,000 |
| SCANNER | ₹1,20,000 |
| COMPUTER | ₹6,16,000 |
Q.2 COUNTA Formula – Total Number of Items
Formula:
=COUNTA(B2:B11)Answer:
10 items
Q.3 COUNTIF Formula – Quantity Analysis
1. Qty greater than 20
Formula:
=COUNTIF(C2:C11,">20")Answer: 3
Items: 35, 25, 22
2. Qty less than 20
Formula:
=COUNTIF(C2:C11,"<20")Answer: 6
Items: 12, 15, 18, 10, 16, 8
3. Qty equal to 20
Formula:
=COUNTIF(C2:C11,"=20")Answer: 1
Item: 20
Q.4 SUMIF Formula – COMPUTER
1. Total Qty of COMPUTER
Formula:
=SUMIF(B2:B11,"COMPUTER",C2:C11)Answer: 47
Calculation:
15 + 10 + 22 = 47
2. Total Rate of COMPUTER
Formula:
=SUMIF(B2:B11,"COMPUTER",D2:D11)Answer: ₹90,000
Calculation:
₹32,000 + ₹30,000 + ₹28,000 = ₹90,000
3. Total Amount of COMPUTER
Formula:
=SUMIF(B2:B11,"COMPUTER",E2:E11)Answer: ₹14,96,000
Calculation:
₹4,80,000 + ₹3,00,000 + ₹6,16,000 = ₹14,96,000
Q.5 IF Formula – Grade
In F2, enter:
=IF(E2>500000,"Expensive","Let's Buy It")Copy the formula down to F11.
Result:
-
Amount > ₹5,00,000 → Expensive
-
Amount ≤ ₹5,00,000 → Let’s Buy It
Expensive items: LAPTOP and COMPUTER (Sr. No. 10)
Q.6 COUNTIF – Number of Expensive Items
Formula:
=COUNTIF(F2:F11,"Expensive")Answer:
2 items
Q.7 SUMIF – Total Amount of Expensive Items
Formula:
=SUMIF(F2:F11,"Expensive",E2:E11)Answer:
₹12,76,000
Calculation:
₹6,60,000 + ₹6,16,000 = ₹12,76,000
Q.8 COUNTIF – Number of COMPUTER Items
Formula:
=COUNTIF(B2:B11,"COMPUTER")Answer:
3 times
Q.9 SUMIF – Amount of Items with Qty > 20
Formula:
=SUMIF(C2:C11,">20",E2:E11)Answer:
₹39,250
Calculation:
-
KEYBOARD → ₹28,000
-
MOUSE → ₹11,250
-
COMPUTER → ₹6,16,000
Total = ₹6,55,250
Correction: The correct answer is ₹6,55,250, not ₹39,250, because the COMPUTER with Qty 22 must also be included.
Q.10 SUM Formula – Grand Total Amount
Formula:
=SUM(E2:E11)Answer:
₹23,09,250
Grand Total:
₹23,09,250