ASSIGNMENT – 42

ANSWER KEY – COUNTIF & COUNTIFS

Q1. Count the Total Number of Students

Formula

=COUNTA(B2:B16)

Answer:

15 Students

Q2. Count Students Who Passed

Formula

=COUNTIF(G2:G16,"PASS")

Answer:

12 Students

Q3. Count Students Who Failed

Formula

=COUNTIF(G2:G16,"FAIL")

Answer:

3 Students

Students who failed:

  • Karan

  • Arjun

  • Vikas

Q4. Count Students with Marks Greater Than 70

Formula

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

Students:

Student Marks
Aarav 85
Meera 72
Pooja 91
Neha 94
Priya 78
Rohan 88
Riya 81

Answer:

7 Students

Q5. Count Students with Attendance 80% or More

Formula

=COUNTIF(F2:F16,">=80")

Answer:

9 Students

Q6. Count Female Students Who Passed

Conditions:

  • Gender = Female

  • Result = PASS

Formula

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

Answer:

8 Students

All 8 female students in the data have a PASS result.

Q7. Count Male Students with Marks Greater Than 70

Conditions:

  • Gender = Male

  • Marks > 70

Formula

=COUNTIFS(C2:C16,"Male",E2:E16,">70")

Matching Students:

Student Marks
Aarav 85
Rohan 88

Answer:

2 Students

Q8. Count Excel Students Who Passed

Conditions:

  • Course = Excel

  • Result = PASS

Formula

=COUNTIFS(D2:D16,"Excel",G2:G16,"PASS")

Matching Students:

  • Aarav

  • Rahul

  • Neha

  • Priya

Answer:

4 Students

Q9. Marks ≥ 80 AND Attendance ≥ 90%

Formula

=COUNTIFS(E2:E16,">=80",F2:F16,">=90")

Matching Students:

Student Marks Attendance
Aarav 85 92%
Pooja 91 95%
Neha 94 97%
Rohan 88 91%
Riya 81 93%

Answer:

5 Students

Q10. Female Excel Students Who Scored ≥ 80

Conditions:

  • Gender = Female

  • Course = Excel

  • Marks ≥ 80

Formula

=COUNTIFS(C2:C16,"Female",D2:D16,"Excel",E2:E16,">=80")

Matching Student:

Neha – 94 Marks

Answer:

1 Student

BONUS ANSWERS ⭐

Q11. Students with Marks < 50

Formula

=COUNTIF(E2:E16,"<50")

Students:

  • Rahul – 45

  • Karan – 38

  • Arjun – 32

  • Ananya – 49

  • Vikas – 25

Answer:

5 Students

Q12. Students with Attendance < 70%

Formula

=COUNTIF(F2:F16,"<70")

Students:

  • Karan – 68%

  • Arjun – 61%

  • Vikas – 55%

Answer:

3 Students

Q13. Male Students Who Failed

Conditions:

  • Gender = Male

  • Result = FAIL

Formula

=COUNTIFS(C2:C16,"Male",G2:G16,"FAIL")

Students:

  • Karan

  • Arjun

  • Vikas

Answer:

3 Students

Q14. Female Students Who Have Paid Their Fees

Conditions:

  • Gender = Female

  • Fees Paid = Yes

Formula

=COUNTIFS(C2:C16,"Female",H2:H16,"Yes")

Students:

  • Meera

  • Pooja

  • Simran

  • Neha

  • Priya

  • Riya

Answer:

6 Students

Q15. Excel Students with Marks ≥ 80 AND Attendance ≥ 90%

Conditions:

  • Course = Excel

  • Marks ≥ 80

  • Attendance ≥ 90%

Formula

=COUNTIFS(D2:D16,"Excel",E2:E16,">=80",F2:F16,">=90")

Matching Students:

Student Marks Attendance
Aarav 85 92%
Neha 94 97%

Answer:

2 Students

FINAL ANSWER SUMMARY

Question Answer
Q1 15 Students
Q2 12 Passed
Q3 3 Failed
Q4 7 Students
Q5 9 Students
Q6 8 Female Students
Q7 2 Male Students
Q8 4 Excel Students
Q9 5 Students
Q10 1 Female Excel Student
Q11 5 Students
Q12 3 Students
Q13 3 Male Students
Q14 6 Female Students
Q15 2 Excel Students

KEY CONCEPT REVISION

COUNTIF → One Condition

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

COUNTIFS → Multiple Conditions

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

Comparison Operators

Operator Meaning
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To
= Equal To
<> Not Equal To

Remember: COUNTIF is for a single criterion, while COUNTIFS is used when two or more conditions must be satisfied.

PreviousNext