ASSIGNMENT – 21

Use of Formulas – COUNTIF, SUMIF & VLOOKUP

Employee Salary Database

Enter the following data in Excel:

EMP NAME SALARY DEPARTMENT EMP ID
Aman ₹72,500 Sales EMP001
Neha ₹84,200 Operations EMP002
Rahul ₹56,800 Marketing EMP003
Priya ₹79,600 HR EMP004
Karan ₹63,500 Finance EMP005
Riya ₹58,900 IT EMP006
Mohit ₹91,300 Marketing EMP007
Anjali ₹76,450 IT EMP008
Vikas ₹54,700 Sales EMP009
Pooja ₹68,250 Finance EMP010
Arjun ₹95,500 Operations EMP011
Simran ₹82,750 Finance EMP012
Rohit ₹61,900 Marketing EMP013
Kavya ₹88,400 Sales EMP014
Nitin ₹57,600 IT EMP015
Divya ₹90,250 Operations EMP016
Manish ₹66,800 Sales EMP017
Tanya ₹74,500 Marketing EMP018
Suresh ₹71,350 HR EMP019
Meena ₹93,200 Finance EMP020

Search Table

Use VLOOKUP to fill the Salary column.

EMP ID SALARY
EMP001 72,500
EMP002  
EMP003  
EMP004  
EMP005  
EMP006  
EMP007  
EMP008  
EMP009  
EMP010  
EMP011  
EMP012  
EMP013  
EMP014  
EMP015  
EMP016  
EMP017  
EMP018  
EMP019  
EMP020  

Questions

Q.1 How many employees work in the HR, IT and Marketing departments?

Use: COUNTIF

Q.2 Find the Salary of Arjun using the SUMIF formula.

Use: SUMIF

Q.3 Calculate the total Salary of employees working in the IT department.

Use: SUMIF

Q.4 Calculate the total Salary of employees working in the Marketing department.

Use: SUMIF

Q.5 Find the Salary of EMP007 using the VLOOKUP function.

Q.6 Find the Salary of EMP014 using the VLOOKUP function.

Q.7 How many employees are working in the Sales department?

Use: COUNTIF

Q.8 How many employees have a Salary greater than ₹80,000?

Use: COUNTIF

Q.9 Calculate the total Salary of employees working in Finance and Operations departments.

Use: SUMIF

Q.10 Using VLOOKUP, find the Salary of:

  • EMP003

  • EMP011

  • EMP020

Formula Hints

COUNTIF

=COUNTIF(C2:C21,"IT")

SUMIF by Department

=SUMIF(C2:C21,"IT",B2:B21)

SUMIF by Employee Name

=SUMIF(A2:A21,"Arjun",B2:B21)

COUNTIF with Condition

=COUNTIF(B2:B21,">80000")

VLOOKUP

If Employee ID is entered in F2:

=VLOOKUP(F2,D2:B21,3,FALSE)

Important: Because the lookup column EMP ID is in column D while Salary is in column B, standard VLOOKUP cannot look to the left. Therefore, for the VLOOKUP questions, arrange the lookup table as EMP ID | EMP NAME | SALARY | DEPARTMENT, or use a separate lookup table with EMP ID as the first column.

Learning Objective

After completing this assignment, students will be able to:

  • Count records using COUNTIF

  • Calculate conditional totals using SUMIF

  • Perform data lookup using VLOOKUP

  • Work with employee databases

  • Apply formulas to practical business data

PreviousNext