ASSIGNMENT – 42

COUNTIF & COUNTIFS – ADVANCED PRACTICE

Objective

In this assignment, you will learn how to use:

  • COUNTIF

  • COUNTIFS

  • Single Criteria

  • Multiple Criteria

  • Comparison Operators

  • Text Criteria

  • Number Criteria

  • Multiple Conditions

STUDENT PERFORMANCE DATA

Roll No. Student Name Gender Course Marks Attendance % Result Fees Paid
101 Aarav Male Excel 85 92 PASS Yes
102 Meera Female Word 72 88 PASS Yes
103 Rahul Male Excel 45 76 PASS No
104 Pooja Female PowerPoint 91 95 PASS Yes
105 Karan Male Excel 38 68 FAIL No
106 Simran Female Word 66 82 PASS Yes
107 Mohit Male PowerPoint 55 74 PASS No
108 Neha Female Excel 94 97 PASS Yes
109 Arjun Male Word 32 61 FAIL No
110 Priya Female Excel 78 89 PASS Yes
111 Rohan Male PowerPoint 88 91 PASS Yes
112 Ananya Female Word 49 79 PASS No
113 Vikas Male Excel 25 55 FAIL No
114 Riya Female PowerPoint 81 93 PASS Yes
115 Nitin Male Word 69 85 PASS Yes

QUESTIONS

Q1. Count the Total Number of Students

Use:

COUNTIF / COUNTA

Find the total number of students in the table.

Q2. Count Students Who Passed

Find how many students have PASS as their Result.

Function: COUNTIF

Q3. Count Students Who Failed

Find the number of students whose Result is FAIL.

Function: COUNTIF

Q4. Count Students with Marks Greater Than 70

Find how many students scored more than 70 marks.

Function: COUNTIF

Q5. Count Students with Attendance 80% or More

Find how many students have attendance of 80% or above.

Function: COUNTIF

Q6. Count Female Students Who Passed

Find the number of Female students who passed.

Function: COUNTIFS

Conditions:

  • Gender = Female

  • Result = PASS

Q7. Count Male Students with Marks Greater Than 70

Find the number of Male students who scored more than 70.

Function: COUNTIFS

Q8. Count Excel Students Who Passed

Find how many students:

  • Course = Excel

  • Result = PASS

Function: COUNTIFS

Q9. Count Students with Marks ≥ 80 AND Attendance ≥ 90%

Find the number of students who satisfy both conditions:

  • Marks ≥ 80

  • Attendance ≥ 90%

Function: COUNTIFS

Q10. Count Female Excel Students Who Scored ≥ 80

Find the number of students who satisfy all three conditions:

  • Gender = Female

  • Course = Excel

  • Marks ≥ 80

Function: COUNTIFS

BONUS CHALLENGE ⭐

Q11.

How many students have Marks < 50?

Q12.

How many students have Attendance < 70%?

Q13.

How many Male students failed?

Q14.

How many Female students have paid their fees?

Q15.

How many Excel students scored ≥ 80 AND Attendance ≥ 90%?

FUNCTIONS TO PRACTICE

COUNTIF
COUNTIFS
COUNTA

Important Difference

COUNTIF → Used for one condition

Example:

=COUNTIF(E2:E16,">70")

COUNTIFS → Used for multiple conditions

Example:

=COUNTIFS(C2:C16,"Female",G2:G16,"PASS")

Student Task

Complete all questions in Excel and make sure that every formula uses the correct range and criteria.

PreviousNext