| SR. NO. | ITEMS | QTY | RATE | AMOUNT | GRADE |
|---|---|---|---|---|---|
| 1 | LAPTOP | 12 | 55,000 | 660,000 | Expensive |
| 2 | COMPUTER | 15 | 32,000 | 480,000 | Let’s Buy It |
| 3 | PRINTER | 20 | 8,500 | 170,000 | Let’s Buy It |
| 4 | MONITOR | 18 | 12,000 | 216,000 | Let’s Buy It |
| 5 | KEYBOARD | 35 | 800 | 28,000 | Let’s Buy It |
| 6 | MOUSE | 450 | 25 | 11,250 | Let’s Buy It |
| 7 | COMPUTER | 10 | 30,000 | 300,000 | Let’s Buy It |
| 8 | UPS | 16 | 6,500 | 104,000 | Let’s Buy It |
| 9 | SCANNER | 8 | 15,000 | 120,000 | Let’s Buy It |
| 10 | COMPUTER | 22 | 28,000 | 616,000 | Expensive |
Answers
Q.1 Amount Formula:
Formula in E2: =PRODUCT(C2, D2) or =C2*D2
(Drag down to calculate all rows)
Q.2 Total Number of Items:
Formula: =COUNTA(B2:B11)
Answer = 10
Q.3 COUNTIF Formulas:
Qty > 20: =COUNTIF(C2:C11, ">20") | Answer = 3
Qty < 20: =COUNTIF(C2:C11, "<20") | Answer = 6
Qty = 20: =COUNTIF(C2:C11, 20) | Answer = 1
Q.4 SUMIF for COMPUTER:
Total Qty: =SUMIF(B2:B11, "COMPUTER", C2:C11) | Answer = 47
Total Rate: =SUMIF(B2:B11, "COMPUTER", D2:D11) | Answer = 90,000
Total Amount: =SUMIF(B2:B11, "COMPUTER", E2:E11) | Answer = 1,396,000
Q.5 Grade Formula (Nested IF):
Formula in F2: =IF(E2>500000, "Expensive", "Let's Buy It")
(Drag down to calculate all rows)
Q.6 Items graded “Expensive”:
Formula: =COUNTIF(F2:F11, "Expensive")
Answer = 2
Q.7 Total Amount of “Expensive” items:
Formula: =SUMIF(F2:F11, "Expensive", E2:E11)
Answer = 1,276,000
Q.8 COMPUTER occurrences:
Formula: =COUNTIF(B2:B11, "COMPUTER")
Answer = 3
Q.9 Total Amount of Qty > 20:
Formula: =SUMIF(C2:C11, ">20", E2:E11)
Answer = 655,250
Q.10 Grand Total Amount:
Formula: =SUM(E2:E11)
Answer = 2,705,250