ASSIGNMENT – 39

USE OF COUNTA, VLOOKUP, COUNTIF, COUNTIFS & IF

Objective

In this assignment, you will learn how to use:

  • COUNTA – to count non-empty cells.

  • VLOOKUP – to find information from a table.

  • COUNTIF – to count cells based on one condition.

  • COUNTIFS – to count cells based on multiple conditions.

  • IF – to calculate Commission based on a condition.

  • Conditional Formatting – to highlight data based on conditions.

PART A – EMPLOYEE SALES DATA

Enter the following data in Excel:

Salesman Jan Feb Mar Apr May June Sales Target Result Commission
Amit 2,500 1,800 1,200 2,200 1,500 1,800   10,000    
Neha 4,200 1,600 900 1,800 1,400 2,500   12,000    
Rahul 1,800 2,200 1,600 2,800 1,900 3,000   15,000    
Pooja 3,200 1,400 2,100 3,500 1,800 2,500   12,000    
Karan 900 1,200 1,800 1,600 1,300 1,500   10,000    
Simran 2,800 2,600 1,700 2,400 2,200 3,000   14,000    
Arjun 1,500 1,900 1,300 2,000 1,600 1,800   10,000    
Neeraj 3,500 2,800 1,900 3,000 2,500 3,200   16,000    
Priya 2,200 1,700 1,100 2,300 1,800 2,100   11,000    
Mohit 3,000 2,500 2,000 2,700 2,300 2,900   15,000    

PART B – SALES CALCULATION

Sales Column

Calculate the total sales of each salesman from January to June.

Formula:

=SUM(B2:G2)

Copy the formula down for all employees.

Result Column

Compare Sales with Target.

If Sales are greater than or equal to Target:

Achieved

Otherwise:

Not Achieved

Formula:

=IF(H2>=I2,"Achieved","Not Achieved")

QUESTIONS

Q1. COUNTA

How many Salesmen are in the list?

Use the COUNTA function.

Hint:

=COUNTA(A2:A11)

Q2. VLOOKUP

Find the following information for Simran:

  • Sales

  • Target

  • Result

Use VLOOKUP.

Hint:

For Sales:

=VLOOKUP("Simran",$A$2:$J$11,8,FALSE)

For Target:

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

For Result:

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

Q3. COUNTIF – Target Achieved

How many Salesmen achieved their target?

Use COUNTIF.

Hint:

=COUNTIF(J2:J11,"Achieved")

Q4. COUNTIF – Target Not Achieved

How many Salesmen did NOT achieve their target?

Use COUNTIF.

Hint:

=COUNTIF(J2:J11,"Not Achieved")

Q5. COUNTIFS – Multiple Conditions

Find the number of Salesmen who have:

  • January Sales > 2,000

  • March Sales <= 1,500

Use COUNTIFS.

Formula:

=COUNTIFS(B2:B11,">2000",D2:D11,"<=1500")

Q6. COUNTIFS – Multiple Conditions

Find the number of Salesmen who have:

  • February Sales > 2,000

  • June Sales > 2,500

Use COUNTIFS.

Formula:

=COUNTIFS(C2:C11,">2000",G2:G11,">2500")

PART C – CONDITIONAL FORMATTING

Q7. Highlight January Sales

Apply Conditional Formatting to the January Sales column.

Conditions:

  • Greater than or equal to 2,000

  • Less than or equal to 3,000

  • Font: Red

  • Background: Yellow

Steps:

  1. Select the January 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:

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

  1. Set:

    • Font Color → Red

    • Fill Color → Yellow

  2. Click OK.

PART D – IF FUNCTION & COMMISSION

Q8. Calculate Commission

If:

Sales >= Target → Commission = 10% of Sales

Otherwise:

Commission = 5% of Sales

Formula:

=IF(H2>=I2,H2*10%,H2*5%)

Copy the formula down for all Salesmen.

PART E – VLOOKUP PRACTICE

Q9. Find Target and Result

Using VLOOKUP, find the following information for:

  • Rahul

  • Pooja

  • Neha

Find:

  1. Target

  2. Result

Target Formula:

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

Result Formula:

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

Repeat the same process for Pooja and Neha.

Q10. COUNTIFS – Three Conditions

Find how many Salesmen have:

  • January Sales > 2,000

  • February Sales > 1,500

  • March Sales < 2,000

Use COUNTIFS.

Formula:

=COUNTIFS(B2:B11,">2000",C2:C11,">1500",D2:D11,"<2000")

BONUS PRACTICE

Q11. Find the Salesman with the highest total Sales.

Q12. Find the Salesman with the lowest total Sales.

Q13. Calculate the average total Sales using AVERAGE.

Q14. Count how many Salesmen have January Sales greater than 3,000.

Q15. Count how many Salesmen achieved their target and have June Sales greater than 2,500.

LEARNING OUTCOME

After completing this assignment, students will be able to:

  1. Count employees using COUNTA.

  2. Calculate total sales using SUM.

  3. Use VLOOKUP with exact match.

  4. Use COUNTIF for single-condition counting.

  5. Use COUNTIFS for multiple conditions.

  6. Use IF for commission calculations.

  7. Apply multiple-condition Conditional Formatting.

  8. Analyze employee sales performance using Excel formulas.

PreviousNext