ASSIGNMENT – 12
Use of Formulas – SUM, COUNTA, COUNTIF, SUMIF & VLOOKUP
Monthly Fruit Sales Report
| PRODUCT NAME | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | TOTAL SALES |
|---|---|---|---|---|---|---|---|---|---|
| Apples | ₹8,500 | ₹12,750 | ₹6,250 | ₹9,800 | ₹15,600 | ₹18,200 | ₹21,450 | ₹17,300 | ? |
| Bananas | ₹11,200 | ₹9,450 | ₹14,800 | ₹12,600 | ₹16,250 | ₹13,500 | ₹19,750 | ₹15,900 | ? |
| Grapes | ₹7,800 | ₹13,200 | ₹10,500 | ₹18,600 | ₹12,400 | ₹16,750 | ₹22,300 | ₹19,200 | ? |
| Mangoes | ₹15,500 | ₹18,750 | ₹21,200 | ₹17,400 | ₹25,600 | ₹28,500 | ₹30,200 | ₹26,800 | ? |
| Oranges | ₹6,750 | ₹8,900 | ₹12,300 | ₹14,500 | ₹10,800 | ₹13,250 | ₹16,400 | ₹11,750 | ? |
| Peaches | ₹9,600 | ₹11,500 | ₹7,850 | ₹13,200 | ₹14,750 | ₹12,600 | ₹18,300 | ₹15,450 | ? |
| Pears | ₹10,800 | ₹14,250 | ₹9,600 | ₹16,400 | ₹13,750 | ₹15,900 | ₹20,500 | ₹18,250 | ? |
| Pineapples | ₹13,400 | ₹16,800 | ₹12,750 | ₹19,300 | ₹17,600 | ₹21,500 | ₹24,200 | ₹20,750 | ? |
| Papaya | ₹8,250 | ₹10,600 | ₹11,900 | ₹13,750 | ₹12,300 | ₹14,800 | ₹17,600 | ₹16,250 | ? |
| Watermelon | ₹12,500 | ₹15,700 | ₹18,900 | ₹20,500 | ₹22,750 | ₹25,600 | ₹29,300 | ₹27,800 | ? |
Questions
Q.1 How many fruits/products are there in the list?
Use of: COUNTA
Q.2 Calculate the Total Sales of every fruit from January to August.
Use of: SUM
Q.3 Find the Total Sales of Mangoes using VLOOKUP.
Q.4 Find the Total Sales of Pineapples using VLOOKUP.
Q.5 Using VLOOKUP, find the March Sales of Grapes.
Q.6 Using VLOOKUP, find the July Sales of Watermelon.
Q.7 Using COUNTIF, count how many fruits have January Sales greater than ₹10,000.
Q.8 Using SUMIF, calculate the Total Sales of fruits whose Total Sales are greater than ₹1,00,000.
Q.9 Using VLOOKUP, find the May and August Sales of Apples.
Q.10 Using SUM, calculate the Grand Total Sales of all fruits from January to August.