ASSIGNMENT – 15
Answer Key – COUNTIF, COUNTIFS, SUMIFS & VLOOKUP
Data Range
-
Name:
A2:A16 -
Gender:
B2:B16 -
Country:
C2:C16 -
Score:
D2:D16
Q.1 How many Male and Female candidates are there?
Male
=COUNTIF(B2:B16,"Male")Answer: 8 Male candidates
Female
=COUNTIF(B2:B16,"Female")Answer: 7 Female candidates
| Gender | Count |
|---|---|
| Male | 8 |
| Female | 7 |
| Total | 15 |
Q.2 How many Male candidates belong to India?
=COUNTIFS(B2:B16,"Male",C2:C16,"India")Male candidates from India:
-
Aarav – 78
-
Karan – 64
Answer: 2 candidates
Q.3 How many Female candidates belong to the USA?
=COUNTIFS(B2:B16,"Female",C2:C16,"USA")Female candidate from USA:
-
Neha – 79
Answer: 1 candidate
Q.4 Find the Country of Priya and Karan.
Priya
=VLOOKUP("Priya",A2:D16,3,FALSE)Answer: Canada
Karan
=VLOOKUP("Karan",A2:D16,3,FALSE)Answer: India
| Name | Country |
|---|---|
| Priya | Canada |
| Karan | India |
Q.5 Find the Score of Ananya and David.
Ananya
=VLOOKUP("Ananya",A2:D16,4,FALSE)Answer: 95
David
=VLOOKUP("David",A2:D16,4,FALSE)Answer: 82
| Name | Score |
|---|---|
| Ananya | 95 |
| David | 82 |
Q.6 Total Score of Male candidates from India
=SUMIFS(D2:D16,B2:B16,"Male",C2:C16,"India")Calculation:
Aarav = 78
Karan = 64
78 + 64 = 142
Answer: 142
Q.7 Total Score of Female candidates from USA
=SUMIFS(D2:D16,B2:B16,"Female",C2:C16,"USA")Female candidate from USA:
Neha = 79
Answer: 79
Q.8 How many candidates have a Score greater than 75?
=COUNTIF(D2:D16,">75")Candidates scoring above 75:
| Name | Score |
|---|---|
| Aarav | 78 |
| Emma | 91 |
| Priya | 84 |
| Ananya | 95 |
| David | 82 |
| Sophia | 88 |
| Neha | 79 |
| Pooja | 87 |
| Ryan | 76 |
Answer: 9 candidates
Q.9 How many Male candidates have a Score greater than 70?
=COUNTIFS(B2:B16,"Male",D2:D16,">70")Male candidates scoring above 70:
| Name | Score |
|---|---|
| Aarav | 78 |
| Michael | 73 |
| David | 82 |
| Rahul | 71 |
| Ryan | 76 |
Answer: 5 candidates
Q.10 Using VLOOKUP, find Gender, Country and Score of Rahul, Neha and Arjun.
Rahul
Gender
=VLOOKUP("Rahul",A2:D16,2,FALSE)Male
Country
=VLOOKUP("Rahul",A2:D16,3,FALSE)Australia
Score
=VLOOKUP("Rahul",A2:D16,4,FALSE)71
Neha
Gender
=VLOOKUP("Neha",A2:D16,2,FALSE)Female
Country
=VLOOKUP("Neha",A2:D16,3,FALSE)USA
Score
=VLOOKUP("Neha",A2:D16,4,FALSE)79
Arjun
Gender
=VLOOKUP("Arjun",A2:D16,2,FALSE)Male
Country
=VLOOKUP("Arjun",A2:D16,3,FALSE)Canada
Score
=VLOOKUP("Arjun",A2:D16,4,FALSE)59
Q.10 Final Answer Table
| Name | Gender | Country | Score |
|---|---|---|---|
| Rahul | Male | Australia | 71 |
| Neha | Female | USA | 79 |
| Arjun | Male | Canada | 59 |
Final Answer Summary
| Q. No. | Answer |
|---|---|
| Q1 | Male = 8, Female = 7 |
| Q2 | Male + India = 2 |
| Q3 | Female + USA = 1 |
| Q4 | Priya = Canada, Karan = India |
| Q5 | Ananya = 95, David = 82 |
| Q6 | Male + India Total Score = 142 |
| Q7 | Female + USA Total Score = 79 |
| Q8 | Score > 75 = 9 candidates |
| Q9 | Male + Score > 70 = 5 candidates |
| Q10 | Rahul, Neha & Arjun details = see table above |