ASSIGNMENT – 21

COUNTIF, SUMIF & VLOOKUP — Answer Key

Data Range

  • Employee Name: A2:A21

  • Salary: B2:B21

  • Department: C2:C21

  • Employee ID: D2:D21

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

HR

=COUNTIF(C2:C21,"HR")

Answer: 2 employees

IT

=COUNTIF(C2:C21,"IT")

Answer: 3 employees

Marketing

=COUNTIF(C2:C21,"Marketing")

Answer: 4 employees

Final Answer

Department Number of Employees
HR 2
IT 3
Marketing 4

Q.2 Find the Salary of Arjun using SUMIF

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

Answer: ₹95,500

Q.3 Total Salary of IT Department

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

Answer: ₹1,92,950

Calculation:

₹58,900 + ₹76,450 + ₹57,600 = ₹1,92,950

Q.4 Total Salary of Marketing Department

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

Answer: ₹2,84,500

Calculation:

₹56,800 + ₹91,300 + ₹61,900 + ₹74,500 = ₹2,84,500

Q.5 Salary of EMP007 using VLOOKUP

For VLOOKUP, use a lookup table where EMP ID is the first column.

=VLOOKUP("EMP007",D2:B21,3,FALSE)

Important: This formula will not work because VLOOKUP cannot look to the left.

Correct VLOOKUP Method

Create a separate lookup table:

EMP ID EMP NAME SALARY DEPARTMENT
EMP001 Aman ₹72,500 Sales
EMP002 Neha ₹84,200 Operations
… … … …
EMP007 Mohit ₹91,300 Marketing

Then use:

=VLOOKUP(F2,H2:K21,3,FALSE)

Answer: ₹91,300

Q.6 Salary of EMP014

=VLOOKUP(F3,H2:K21,3,FALSE)

Answer: ₹88,400

Q.7 How many employees are working in Sales?

=COUNTIF(C2:C21,"Sales")

Answer: 4 employees

Sales employees:

  • Aman

  • Vikas

  • Kavya

  • Manish

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

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

Answer: 7 employees

Employees earning more than ₹80,000:

Employee Salary
Neha ₹84,200
Mohit ₹91,300
Arjun ₹95,500
Simran ₹82,750
Kavya ₹88,400
Divya ₹90,250
Meena ₹93,200

Total = 7 employees

Q.9 Total Salary of Finance and Operations Departments

Finance

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

Finance Total = ₹3,07,700

Operations

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

Operations Total = ₹2,69,950

Combined Total

=SUMIF(C2:C21,"Finance",B2:B21)+SUMIF(C2:C21,"Operations",B2:B21)

Combined Total = ₹5,77,650

Department Total Salary
Finance ₹3,07,700
Operations ₹2,69,950
Combined Total ₹5,77,650

Q.10 Find Salary of EMP003, EMP011 and EMP020 using VLOOKUP

Assuming the separate lookup table is in H2:K21:

EMP003

=VLOOKUP("EMP003",H2:K21,3,FALSE)

Answer: ₹56,800

EMP011

=VLOOKUP("EMP011",H2:K21,3,FALSE)

Answer: ₹95,500

EMP020

=VLOOKUP("EMP020",H2:K21,3,FALSE)

Answer: ₹93,200

Final Answer Summary

Q. Answer
Q1 HR = 2, IT = 3, Marketing = 4
Q2 Arjun = ₹95,500
Q3 IT Total = ₹1,92,950
Q4 Marketing Total = ₹2,84,500
Q5 EMP007 = ₹91,300
Q6 EMP014 = ₹88,400
Q7 Sales Employees = 4
Q8 Salary > ₹80,000 = 7 employees
Q9 Finance = ₹3,07,700, Operations = ₹2,69,950, Combined = ₹5,77,650
Q10 EMP003 = ₹56,800, EMP011 = ₹95,500, EMP020 = ₹93,200

⭐ Important Excel Point

For this assignment, VLOOKUP requires the lookup column to be the first column of the selected table. Since EMP ID is originally in column D and SALARY is in column B, students should create a separate lookup table with:

EMP ID → EMP NAME → SALARY → DEPARTMENT

Then VLOOKUP will work correctly.

PreviousNext