ASSIGNMENT – 26

COUNTA, COUNTIF, COUNTIFS, VLOOKUP & INDEX + MATCH — Answer Key

Data Range

  • Date: A2:A21

  • Employee ID: B2:B21

  • Name: C2:C21

  • Designation: D2:D21

  • KRA: E2:E21

Q.1 — Total Employee Records

Formula

=COUNTA(C2:C21)

Answer:

20 employee records

Q.2 — Number of Sales Executives

Formula

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

Answer:

11 employees

Sales Executives:

  1. AMAN

  2. NEHA

  3. SIMRAN

  4. POOJA

  5. ANJALI

  6. MOHIT

  7. VIKAS

  8. KAVYA

  9. RAVI

  10. NITIN

  11. SURESH

Q.3 — Employees Having Amazon as KRA

Formula

=COUNTIF(E2:E21,"AMAZON")

Answer:

5 employees

They are:

  • AMAN

  • SIMRAN

  • MOHIT

  • VIKAS

  • NITIN

Q.4 — Sales Executives Working for Amazon

Formula

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

Answer:

5 employees

Employee Designation KRA
AMAN SALES EXECUTIVE AMAZON
SIMRAN SALES EXECUTIVE AMAZON
MOHIT SALES EXECUTIVE AMAZON
VIKAS SALES EXECUTIVE AMAZON
NITIN SALES EXECUTIVE AMAZON

Q.5 — Sales Executives Working for Myntra

Formula

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

Answer:

4 employees

Employee Designation KRA
NEHA SALES EXECUTIVE MYNTRA
POOJA SALES EXECUTIVE MYNTRA
KAVYA SALES EXECUTIVE MYNTRA
SURESH SALES EXECUTIVE MYNTRA

Q.6 — Sales Executives for Amazon OR Myntra

Because COUNTIFS handles multiple criteria as AND, calculate the two KRA groups separately and add them.

Formula

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

Calculation:

Amazon Sales Executives = 5

Myntra Sales Executives = 4

Total = 5 + 4 = 9

Answer:

9 employees

Q.7 — Designation and KRA of NEHA

Designation

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

Answer: SALES EXECUTIVE

KRA

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

Answer: MYNTRA

Final Answer:

NEHA → SALES EXECUTIVE → MYNTRA

Q.8 — Designation and KRA of NITIN

Designation

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

Answer: SALES EXECUTIVE

KRA

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

Answer: AMAZON

Final Answer:

NITIN → SALES EXECUTIVE → AMAZON

Q.9 — Employee ID of MOHIT

Formula

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

Answer:

1209

Q.10 — Employee ID of DIVYA and RIYA

DIVYA

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

Answer: 1215

RIYA

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

Answer: 1219

Final Answer Summary

Q. Question Answer
Q.1 Total employee records 20
Q.2 Sales Executives 11
Q.3 Employees with Amazon KRA 5
Q.4 Sales Executive + Amazon 5
Q.5 Sales Executive + Myntra 4
Q.6 Sales Executive + Amazon OR Myntra 9
Q.7 NEHA – Designation SALES EXECUTIVE
  NEHA – KRA MYNTRA
Q.8 NITIN – Designation SALES EXECUTIVE
  NITIN – KRA AMAZON
Q.9 MOHIT – Employee ID 1209
Q.10 DIVYA – Employee ID 1215
  RIYA – Employee ID 1219

⭐ Important Learning Point

COUNTIF → One condition

=COUNTIF(range,criteria)

COUNTIFS → Multiple conditions

=COUNTIFS(range1,criteria1,range2,criteria2)

VLOOKUP → Find information from a table

=VLOOKUP(lookup_value,table_array,column_number,FALSE)

INDEX + MATCH → Find the position and return the corresponding value

=INDEX(return_range,MATCH(lookup_value,lookup_range,0))
PreviousNext