0

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

Community
  • 1
  • 1
user3405976
  • 51
  • 1
  • 2
  • 8
  • Get started with some [COUNTIF](https://support.office.com/en-US/article/COUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34) and [COUNTIFS functions](https://support.office.com/en-us/article/COUNTIFS-function-53C4DC8E-0E5B-4E32-93DF-9CA5E7DA89ED). Edit your question to include your own effort and a description of any problems or inadequacies if you run into trouble. –  Aug 14 '15 at 11:42
  • @Jeeped- Thanks for your response. I will try to work on your suggestions , however I am a new bie to advance Excel tools. – user3405976 Aug 14 '15 at 12:04

2 Answers2

2

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

Community
  • 1
  • 1
ManishChristian
  • 3,759
  • 3
  • 22
  • 50
  • Thank you for the response. You're code is working fine, but it is calculating each present(a "P" in this case) which I have already done with SUMPRODUCT. 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 lets say 20 days in a month, the total number will be 1 and not 20. I hope I made things clear. I have also edited my question. – user3405976 Aug 14 '15 at 14:43
  • My answer was based on your question (without edit) and based on my understanding. You still can get all the counts, you just need to change logic in formula. – ManishChristian Aug 14 '15 at 15:34
  • Yeah ! I tried a lot to change the logic, but failed. – user3405976 Aug 14 '15 at 15:48
  • You are talking about so many formulas for your so many scenarios. Show us what have you tried, what is failing, etc. so that we can help you with that. – ManishChristian Aug 14 '15 at 18:48
0

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")
Shalev Shalit
  • 1,945
  • 4
  • 24
  • 34
Akyare
  • 1
  • 1
  • Kindly see my EDITED question – user3405976 Aug 14 '15 at 14:46
  • You can accomplish this by adding a column with this formula =if(COUNTIF($D2:$L2;"A")<>0;0;1) This formula will have as result 1 if there is no “A”. Then sum or sumif, sumifs will provide you the results you want. – Akyare Aug 14 '15 at 17:09