| SR. NO. | ITEMS | QTY | RATE | AMOUNT | GRADE |
|---|---|---|---|---|---|
| 1 | LAPTOP | 12 | 55000 | ||
| 2 | COMPUTER | 15 | 32000 | ||
| 3 | PRINTER | 20 | 8500 | ||
| 4 | MONITOR | 18 | 12000 | ||
| 5 | KEYBOARD | 35 | 800 | ||
| 6 | MOUSE | 450 | 25 | ||
| 7 | COMPUTER | 10 | 30000 | ||
| 8 | UPS | 16 | 6500 | ||
| 9 | SCANNER | 8 | 15000 | ||
| 10 | COMPUTER | 22 | 28000 |
Questions
Q.1 Use the PRODUCT formula to calculate Amount = Qty * Rate for all items.
Q.2 Use the COUNTA formula to calculate the total number of items in the list.
Q.3 Using COUNTIF, calculate:
- How many items have Qty greater than 20 (>20)?
- How many items have Qty less than 20 (<20)?
- How many items have Qty equal to 20 (=20)?
Q.4 Using the SUMIF formula, calculate the total:
- Qty of COMPUTER
- Rate of COMPUTER
- Amount of COMPUTER
Q.5 Using the IF formula, display “Expensive” if the Amount is greater than ₹5,00,000, otherwise display “Let’s Buy It”.
Q.6 Using COUNTIF, calculate how many items have the Grade “Expensive”.
Q.7 Using SUMIF, calculate the total Amount of all items whose Grade is “Expensive”.
Q.8 Using COUNTIF, calculate how many times COMPUTER appears in the Items column.
Q.9 Using SUMIF, calculate the total Amount of all items having Qty greater than 20.
Q.10 Find the Grand Total Amount of all the items using the SUM formula.