ASSIGNMENT – 10

Use of Formulas – COUNTA, COUNTIF, SUMIF & VLOOKUP

Part A – Use of VLOOKUP

Fill in the following table using the VLOOKUP function.

Employee ID Full Name SSN Department Start Date Earnings
EMP001          
EMP002          
EMP003          
EMP004          
EMP005          

Part B – Employee Database

Use the following Employee Database to answer the questions.

Employee ID Full Name SSN Department Start Date Earnings
EMP001 Aarav Sharma 421-56-7832 Marketing 15-01-2020 ₹72,000
EMP002 Meera Kapoor 583-21-6490 Finance 20-02-2020 ₹85,000
EMP003 Rohan Verma 317-84-5261 IT/IS 12-03-2020 ₹95,000
EMP004 Ananya Singh 692-35-4178 Marketing 05-04-2020 ₹1,10,000
EMP005 Kunal Mehta 245-73-8619 Engineering 18-05-2020 ₹92,000
EMP006 Priya Gupta 731-46-2580 Finance 10-06-2020 ₹68,000
EMP007 Aditya Jain 158-92-4736 Engineering 25-07-2020 ₹1,05,000
EMP008 Neha Bansal 864-31-7952 Marketing 14-08-2020 ₹88,000
EMP009 Vivek Joshi 529-67-3148 Finance 03-09-2020 ₹76,000
EMP010 Simran Kaur 406-18-9537 IT/IS 22-10-2020 ₹1,20,000
EMP011 Arjun Malhotra 275-83-6419 Marketing 15-11-2020 ₹98,000
EMP012 Pooja Saini 643-25-8170 Human Resources 08-12-2020 ₹1,15,000
EMP013 Rahul Yadav 918-54-3267 Engineering 17-01-2021 ₹1,32,000
EMP014 Divya Chawla 352-79-4816 Marketing 25-02-2021 ₹82,000

Questions

Q.1

How many employees are there in the Employee Database?

Use of: COUNTA

Q.2

How many employees work in the following departments?

  • Finance

  • Marketing

  • Engineering

Use of: COUNTIF

Q.3

Find the Department and Earnings of employee Kunal Mehta.

Use of: VLOOKUP

Q.4

Find the SSN of employee Kunal Mehta.

Use of: VLOOKUP

Q.5

Find the total Earnings of all employees working in the Marketing department.

Use of: SUMIF

Q.6

Find the Full Name and Department of Employee ID EMP010.

Use of: VLOOKUP

Q.7

Find the Earnings of Employee ID EMP013.

Use of: VLOOKUP

Q.8

How many employees have Earnings greater than ₹1,00,000?

Use of: COUNTIF

Q.9

Find the total Earnings of all employees working in the Finance department.

Use of: SUMIF

Q.10

Using VLOOKUP, find the following details for Employee IDs EMP004, EMP008 and EMP012:

  • Full Name

  • Department

  • Start Date

  • Earnings

Use of: VLOOKUP

Formulas to Practise

Function Purpose
COUNTA Count the number of non-empty cells
COUNTIF Count cells based on a condition
SUMIF Add values based on a condition
VLOOKUP Find information from a database using a lookup value
PreviousNext