ASSIGNMENT – 5

Sales Report

Use of Formulas – SUM, IF, COUNTA, COUNTIF, SUMIF, VLOOKUP & LOOKUP

Sales Data

Salesman JAN FEB MAR APR MAY JUNE SALES TARGET RESULT
AMAN 2,500 1,800 900 1,600 1,200 1,500   11,000  
VIVEK 4,200 1,500 700 1,300 1,400 2,600   12,000  
RAHUL 3,200 900 1,400 2,800 1,600 3,300   18,000  
PRIYA 1,200 1,100 1,900 450 1,500 1,300   11,000  
ROHIT 700 1,200 2,200 750 1,800 1,600   13,000  
ASHOK 900 700 2,500 2,100 1,900 2,000   10,000  
AJAY 1,400 1,600 1,700 800 2,700 6,500   12,000  
ALOK 1,700 1,900 2,000 1,700 500 1,600   10,000  
AMIT 1,900 2,600 1,800 1,600 2,900 1,900   12,500  
SURESH 300 2,800 2,000 1,400 1,600 2,900   10,000  

Questions

Q.1 COUNTA & VLOOKUP – AJAY

  1. Using the COUNTA formula, calculate the total number of salesmen in the list.

  2. Using the VLOOKUP formula, find the Target of AJAY.

  3. Using the VLOOKUP formula, find the Result of AJAY.

Q.2 SUM Formula – Total Sales

Calculate the total SALES of each salesman using the SUM formula.

Sales = JAN + FEB + MAR + APR + MAY + JUNE

Q.3 IF Formula – Target Result

Using the IF formula, display:

  • “Target Achieved” if Sales is greater than or equal to Target

  • “Not Achieved” if Sales is less than Target

Q.4 VLOOKUP – RAHUL

Using the VLOOKUP formula, find:

  1. Target of RAHUL

  2. Result of RAHUL

Q.5 VLOOKUP – PRIYA

Using the VLOOKUP formula, find:

  1. Target of PRIYA

  2. Result of PRIYA

Q.6 COUNTIF – Target Achieved

Using the COUNTIF formula, calculate how many salesmen have Achieved their Target.

Q.7 COUNTIF – Target Not Achieved

Using the COUNTIF formula, calculate how many salesmen have Not Achieved their Target.

Q.8 LOOKUP – Find Salesman

Using the LOOKUP function, find the name of the salesman whose:

  • January Sales = ₹2,500

  • February Sales = ₹1,800

Q.9 SUMIF – Total Achieved Sales

Using the SUMIF formula, calculate the total Sales of all salesmen who achieved their Target.

Q.10 VLOOKUP – Multiple Salesmen

Using the VLOOKUP formula, find the following details for:

  1. AMAN

  2. ASHOK

  3. AMIT

For each salesman, find:

  • Total Sales

  • Target

  • Result

Formulas to Practice

SUM | IF | COUNTA | COUNTIF | SUMIF | VLOOKUP | LOOKUP

VLOOKUP Lookup Range

Use the complete table as the VLOOKUP range:

A2

The Salesman column will be the lookup column.

Example:

=VLOOKUP("AJAY",$A$2:$J$11,9,FALSE)

This returns AJAY’s Target.

=VLOOKUP("AJAY",$A$2:$J$11,10,FALSE)

This returns AJAY’s Result.

PreviousNext