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:
-
AMAN
-
NEHA
-
SIMRAN
-
POOJA
-
ANJALI
-
MOHIT
-
VIKAS
-
KAVYA
-
RAVI
-
NITIN
-
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))