ASSIGNMENT – 26

Use of Formulas – COUNTA, COUNTIF, COUNTIFS, VLOOKUP & INDEX + MATCH

Employee Database

Enter the following data in Excel:

DATE EMP ID NAME DESIGNATION KRA
01-08-2026 1201 AMAN SALES EXECUTIVE AMAZON
01-08-2026 1202 PRIYA HR EXECUTIVE RECRUITMENT
02-08-2026 1203 ROHAN SOFTWARE ENGINEER PYTHON
02-08-2026 1204 NEHA SALES EXECUTIVE MYNTRA
03-08-2026 1205 KARAN PROGRAMMER JAVA
03-08-2026 1206 SIMRAN SALES EXECUTIVE AMAZON
04-08-2026 1207 RAHUL SOFTWARE ASSOCIATE TESTING
04-08-2026 1208 POOJA SALES EXECUTIVE MYNTRA
05-08-2026 1209 VIKAS NETWORK ENGINEER SERVER
05-08-2026 1210 ANJALI SALES EXECUTIVE FLIPKART
06-08-2026 1211 MOHIT SALES EXECUTIVE AMAZON
06-08-2026 1209 VIKAS SALES EXECUTIVE AMAZON
07-08-2026 1212 KAVYA SALES EXECUTIVE MYNTRA
07-08-2026 1213 ARJUN PROGRAMMER C++
08-08-2026 1214 RAVI SALES EXECUTIVE FLIPKART
08-08-2026 1215 DIVYA HR EXECUTIVE RECRUITMENT
09-08-2026 1216 NITIN SALES EXECUTIVE AMAZON
09-08-2026 1217 MEENA MARKETING MANAGER MARKETING
10-08-2026 1218 SURESH SALES EXECUTIVE MYNTRA
10-08-2026 1219 RIYA SOFTWARE ENGINEER .NET

Note: The database contains 20 employee records. The Employee ID 1209 appears twice for Vikas, intentionally providing a useful example for students to observe duplicate IDs.

Questions

Part A – COUNTA

Q.1 How many employee records are there in the list?

Use: COUNTA

Part B – COUNTIF

Q.2 How many employees are working as Sales Executive?

Use: COUNTIF

Q.3 How many employees have Amazon as their KRA?

Use: COUNTIF

Part C – COUNTIFS

Q.4 How many Sales Executives are working for Amazon?

Use: COUNTIFS

Q.5 How many Sales Executives are working for Myntra?

Use: COUNTIFS

Q.6 Find the total number of employees who are Sales Executives and whose KRA is either Amazon OR Myntra.

Use: COUNTIFS

Hint: Since COUNTIFS applies AND conditions, calculate Amazon and Myntra separately and add the results.

Part D – VLOOKUP

Q.7 Find the Designation and KRA of NEHA using VLOOKUP.

Q.8 Find the Designation and KRA of NITIN using VLOOKUP.

Part E – INDEX + MATCH

Q.9 Find the Employee ID of MOHIT using INDEX + MATCH.

Q.10 Find the Employee ID of DIVYA and RIYA using INDEX + MATCH.

Formula Practice

1. COUNTA – Count Employee Records

To count the employee names:

=COUNTA(C2:C21)

2. COUNTIF – Sales Executives

=COUNTIF(D2:D21,"SALES EXECUTIVE")

3. COUNTIF – Amazon KRA

=COUNTIF(E2:E21,"AMAZON")

4. COUNTIFS – Sales Executive + Amazon

=COUNTIFS(D2:D21,"SALES EXECUTIVE",E2:E21,"AMAZON")

5. COUNTIFS – Sales Executive + Myntra

=COUNTIFS(D2:D21,"SALES EXECUTIVE",E2:E21,"MYNTRA")

6. COUNTIFS – Amazon OR Myntra

=COUNTIFS(D2:D21,"SALES EXECUTIVE",E2:E21,"AMAZON")+COUNTIFS(D2:D21,"SALES EXECUTIVE",E2:E21,"MYNTRA")

VLOOKUP Practice

7. Find NEHA’s Designation

=VLOOKUP("NEHA",C2:E21,2,FALSE)

Result: SALES EXECUTIVE

Find NEHA’s KRA

=VLOOKUP("NEHA",C2:E21,3,FALSE)

Result: MYNTRA

8. Find NITIN’s Designation

=VLOOKUP("NITIN",C2:E21,2,FALSE)

Result: SALES EXECUTIVE

Find NITIN’s KRA

=VLOOKUP("NITIN",C2:E21,3,FALSE)

Result: AMAZON

INDEX + MATCH Practice

9. Find Employee ID of MOHIT

=INDEX(B2:B21,MATCH("MOHIT",C2:C21,0))

10. Find Employee ID of DIVYA and RIYA

DIVYA

=INDEX(B2:B21,MATCH("DIVYA",C2:C21,0))

RIYA

=INDEX(B2:B21,MATCH("RIYA",C2:C21,0))

Skills Covered

After completing this assignment, students will learn:

  • COUNTA – Count non-empty records

  • COUNTIF – Count records based on one condition

  • COUNTIFS – Count records based on multiple conditions

  • VLOOKUP – Retrieve related information

  • INDEX + MATCH – Search for a value and return related data

  • Working with an Employee Database

  • Using Exact Match with FALSE and 0

  • Handling multiple criteria

  • Understanding AND vs OR conditions

  • Working with duplicate data

PreviousNext