ASSIGNMENT – 15

Use of Formulas – COUNTIF, COUNTIFS, SUMIFS & VLOOKUP

Candidate Score Report

Name	Gender	Country	Score
Aarav	Male	India	78
Emma	Female	UK	91
Daniel	Male	USA	68
Priya	Female	Canada	84
Riya	Female	Australia	56
Michael	Male	Canada	73
Ananya	Female	India	95
David	Male	USA	82
Karan	Male	India	64
Sophia	Female	UK	88
Rahul	Male	Australia	71
Neha	Female	USA	79
Arjun	Male	Canada	59
Pooja	Female	India	87
Ryan	Male	UK	76

Questions

Q.1

How many Male and Female candidates are there in the list?

Use of: COUNTIF

Find the count separately for:

• Male

• Female

Q.2

How many Male candidates belong to India?

Use of: COUNTIFS

Q.3

How many Female candidates belong to the USA?

Use of: COUNTIFS

Q.4

Find the Country of Priya and Karan using the VLOOKUP function.

Use of: VLOOKUP

Q.5

Find the Score of Ananya and David using the VLOOKUP function.

Use of: VLOOKUP

Q.6

Calculate the total Score of Male candidates from India.

Use of: SUMIFS

Q.7

Calculate the total Score of Female candidates from the USA.

Use of: SUMIFS

Q.8

How many candidates have a Score greater than 75?

Use of: COUNTIF

Q.9

How many Male candidates have a Score greater than 70?

Use of: COUNTIFS

Q.10

Using VLOOKUP, find the following details for Rahul, Neha and Arjun:

• Gender

• Country

• Score

Use of: VLOOKUP

Functions to Practise

Function	Purpose
COUNTIF	Count records based on one condition
COUNTIFS	Count records based on multiple conditions
SUMIFS	Calculate total based on multiple conditions
VLOOKUP	Find information from a table using a lookup value

Excel Column Structure

Column	Field
A	Name
B	Gender
C	Country
D	Score

Data Range: A1:D16

Basic Syntax

COUNTIF

=COUNTIF(range,criteria)

COUNTIFS

=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)

SUMIFS

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

VLOOKUP

=VLOOKUP(lookup_value,table_array,column_number,FALSE)