ASSIGNMENT – 40

SALESMAN PERFORMANCE ANALYSIS

Objective

In this assignment, you will practice:

  • COUNTA – Count the number of salesmen.

  • VLOOKUP – Find Target and Result from the sales table.

  • COUNTIF – Count records based on one condition.

  • COUNTIFS – Count records based on multiple conditions.

  • XLOOKUP – Find information using a lookup value.

  • Conditional Formatting – Highlight values and results based on conditions.

PART A – SALESMAN PERFORMANCE DATA

Enter the following data in Excel:

Salesman Jan Feb Mar Apr May June Sales Target Result
Ramesh 2,000 1,500 300 1,400 1,000 1,400 7,600 10,000 NOT ACHIEVED
Rakesh 5,000 1,200 500 1,200 1,200 2,800 11,900 12,000 NOT ACHIEVED
Rahul 3,000 800 1,200 3,000 1,500 3,500 13,000 18,000 NOT ACHIEVED
Pooja 1,000 900 1,800 5,000 1,400 1,200 11,300 10,000 ACHIEVED
Manoj 500 1,000 2,300 8,000 1,700 1,400 14,900 12,000 ACHIEVED
Ashok 800 500 2,400 1,900 1,800 1,800 9,200 10,000 NOT ACHIEVED
Ajeet 1,200 1,400 1,500 700 2,500 7,000 14,300 12,000 ACHIEVED
Alok 1,500 1,800 1,800 1,800 300 1,500 8,700 10,000 NOT ACHIEVED
Amrit 1,800 2,500 1,700 1,500 2,800 1,800 12,100 12,000 ACHIEVED
Surendra 200 3,000 1,900 1,200 1,500 3,000 10,800 10,000 ACHIEVED
Shashi 1,600 1,200 2,000 800 1,700 800 8,100 10,000 NOT ACHIEVED

PART B – VLOOKUP & COUNTA

Q1. Count Salesmen

How many salesmen are there in the list?

Use:

=COUNTA(A2:A12)

Q2. VLOOKUP – Ajeet

Using VLOOKUP, find the following information for Ajeet:

  1. Target

  2. Result

Target Formula:

=VLOOKUP("Ajeet",$A$2:$J$12,9,FALSE)

Result Formula:

=VLOOKUP("Ajeet",$A$2:$J$12,10,FALSE)

Q3. VLOOKUP – Multiple Salesmen

Using VLOOKUP, find the following information for:

  • Rahul

  • Pooja

  • Ashok

Find:

  1. Target

  2. Result

Target:

=VLOOKUP("Rahul",$A$2:$J$12,9,FALSE)

Result:

=VLOOKUP("Rahul",$A$2:$J$12,10,FALSE)

Repeat the same process for Pooja and Ashok.

PART C – COUNTIF & COUNTIFS

Q4. Count Achieved Salesmen

How many salesmen have ACHIEVED their target?

Use COUNTIF.

Formula:

=COUNTIF(J2:J12,"ACHIEVED")

Q5. Find Salesman Using Two Conditions

Which salesman had:

  • January Sales = 2,000

  • February Sales = 2,500

Use COUNTIFS to identify the matching record.

COUNTIFS Formula:

=COUNTIFS(B2:B12,2000,C2:C12,2500)

You may also use XLOOKUP or a lookup-based method to return the Salesman Name.

Q6. Multiple Conditions Using COUNTIFS

How many salesmen have:

  • January Sales > 2,000

  • March Sales <= 1,500

Formula:

=COUNTIFS(B2:B12,">2000",D2:D12,"<=1500")

Q7. Count Salesmen with Sales Greater Than 12,000

How many salesmen have Total Sales > 12,000?

Use COUNTIF.

Formula:

=COUNTIF(H2:H12,">12000")

PART D – VLOOKUP / XLOOKUP

Q8. Find Salesman with June Sales = 7,000

Find the salesman whose June Sales are 7,000.

Option 1 – XLOOKUP

=XLOOKUP(7000,G2:G12,A2:A12)

Option 2 – INDEX + MATCH

=INDEX(A2:A12,MATCH(7000,G2:G12,0))

Task:

Find the Salesman’s Name.

PART E – CONDITIONAL FORMATTING

Q9. Highlight Sales Between 2,000 and 5,000

Apply Conditional Formatting to the January–June Sales data.

Condition:

Highlight values:

  • Greater than or equal to 2,000

  • Less than or equal to 5,000

Formatting:

  • Font Color: Red

  • Background: Yellow

Formula:

=AND(B2>=2000,B2<=5000)

Steps:

  1. Select the Jan–June sales range.

  2. Go to Home → Conditional Formatting.

  3. Select New Rule.

  4. Choose Use a formula to determine which cells to format.

  5. Enter the formula.

  6. Set Font Color to Red.

  7. Set Fill Color to Yellow.

  8. Click OK.

Q10. Conditional Formatting – Result Column

Apply Conditional Formatting to the Result column.

Condition 1

If Result = ACHIEVED

→ Green Fill

Condition 2

If Result = NOT ACHIEVED

→ Red Fill

Formula for ACHIEVED:

=J2="ACHIEVED"

Formula for NOT ACHIEVED:

=J2="NOT ACHIEVED"

BONUS PRACTICE

Q11. Find the salesman with the highest total Sales.

Q12. Find the salesman with the lowest total Sales.

Q13. Find how many salesmen have June Sales greater than 2,000.

Q14. Find how many salesmen have:

  • January Sales > 1,000

  • February Sales > 1,000

  • March Sales > 1,000

Use COUNTIFS.

Q15. Find the salesman whose Target is exactly 12,000 using a lookup formula.

LEARNING OUTCOME

After completing this assignment, students will be able to:

  1. Count records using COUNTA.

  2. Find employee information using VLOOKUP.

  3. Count records using COUNTIF.

  4. Apply multiple conditions using COUNTIFS.

  5. Use XLOOKUP for reverse/matching lookups.

  6. Apply Conditional Formatting to numerical ranges.

  7. Highlight text-based results automatically.

  8. Analyze salesman performance using Excel.

PreviousNext