CODEWITHSUNDEEP
excel
user3405976
user3405976

Reputation: 51

How to count students number on different conditions in a attendance sheet

I have been working on an Excel sheet of school attendance. I have already asked a question about that attendance. Now I have a different situation. I have both “boy(B)/girl(G)” students in Gender section. I also have a Category section having 5 different categories. Now I have dates corresponding to each name of the students where a teacher puts either a “P” or an “A”. Please loot into the image:

Attendance sheet

I need to count total number of students on below conditions:

  1. How many boys were present (at least 1 day) in a month?
  2. How many girl were present (at least 1 day) in a month?
  3. How many boys in each category were present (at least 1 day) in a month?
  4. How many girls in each category were present (at least 1 day) in a month?

Kindly note that the names of the students are not unique.

Please help !

EDIT:

I tried to count "total number of male students (B - in my case) attended at least 1 class in this way:

=COUNTIFS(D5:D49,"B",F5:AJ49,"P")

But it is throwing an error.

EDIT:

Thank you all for the responses. But I guess I did not able to make my point clearly.

Here I want to count "How many students were present in all cases and not "how many times each student was present", i.e. I need to count student in all cases who went to school even a single day. So if a student was present let’s say 20 days in a month, the total number will be 1 and not 20.

I hope I made things clear now.

EDIT:

Thank you guys for the help. I have accomplished the task by adding an extra column which returns a 1 if there is any "P" in the date row by using:

=IF((COUNTIF(G5:AK5,"P"))>0,1,0)

Then I am using a COUNTIFS with multiple conditions which checks and count if the extra cell (above) have a value of 1 and checks the Gender and Category row and count it:

=COUNTIFS(C5:C27,">0", E5:E27,"G", F5:F27,"CAT1")

Though, I don't want to use the extra cell as it is not needed for attendance purpose. So I asked a different question here

Upvotes: 0

Views: 26036

Answers (2)

ManishChristian
ManishChristian

Reputation: 3784

You need to use SUM function in this case. Take a look at this question.

Hers is the table with all the formulas.

I've highlighted your questions and put the numbers (1,2,3,4).
So the formulas are:

1. =SUM(IF($D$3:$D$12="b",IF($F$3:$O$12="p",1,0),0))  

2. =SUM(IF($D$3:$D$12="g",IF($F$3:$O$12="p",1,0),0))  

3. =SUM(IF($E$3:$E$12="cat1",IF($D$3:$D$12="b",IF($F$3:$O$12="p",1,0),0)))  

4. =SUM(IF($E$3:$E$12="cat1",IF($D$3:$D$12="g",IF($F$3:$O$12="p",1,0),0)))

In formulas 3 and 4, you just have to change cat1 to cat2 to get the result for category 2. And change cat2 to cat3 and so on.

Basically you are summing the values based on multiple conditions.

Just change the cell references.

In order to make it work you need to type CTRL + SHIFT + ENTER in the cell for it to calculate properly

enter image description here

Upvotes: 2

Akyare
Akyare

Reputation: 1

The issue with the your formula is that your criteria range for the attendees has several columns, you formula would work if you had only one column as criteria range. You can add one total column for the "A" and one total column for the "P".

Formula for Total A=COUNTIF($D2:$L2;"A")
Formula for Total P=COUNTIF($D2:$L2;"P")

And then the sumif will provide you the attendees for B Gender(if you have only one condition)

=SUMIF($B$2:$B$7;"B";$N$2:$N$7)

Where you have in column N the "Total P" and in column B the Gender

And the sumifs will provide you the attendees for B Gender in CAT2 (if hou have more than one condition)

=SUMIFS(N$2:N$7;$B$2:$B$7;"B";$C$2:$C$7;"CAT2")

Upvotes: 0

Related Questions