Multiple Group Attendance Percentage

[Juddman79]

Hi all,

I'm wondering is it possible to work out a percentage of group attendance for 2 different groups (S and SC) that run either in the morning (AM) or afternoon (PM) throughout the week? Not every person is scheduled to attend every session but if we are expecting them then their attendance would be marked with a 'Y' or 'N' on the sheet. If we are not expecting them then the cell is left blank.

Is there a formula/code to get a percentage of the expected attendance for both types of groups for the week if they either run in the morning or afternoon. Basically 4 different percentages totals (Cells W14 - Z14)

I hope that makes sense.

Also, just as a quick second question is there also a formula or code to auto capitalise the range of cells which will be populated with a 'Y' or 'N' in them?

Thanks in advance

 

[BrerRabbit]

At the bottom of each column - G40 =countif(G14:G38,"Y") G41 (countif(G14:G38,"*"). And then for the rest of the row.

W14 = (sumifs(range with count Y,G12:T12,"S",G11:T11,"AM"))/(sumifs(range with count "*",G12:T12,"S",G11:T11,"AM")). Format W14 as a percentage.

 

[Juddman79]

At the bottom of each column - G40 =countif(G14:G38,"Y") G41 (countif(G14:G38,"*"). And then for the rest of the row.

W14 = (sumifs(range with count Y,G12:T12,"S",G11:T11,"AM"))/(sumifs(range with count "*",G12:T12,"S",G11:T11,"AM")). Format W14 as a percentage.

Thank you so much, works great.

Just wondering is there a way to add to the formula to stop the #DIV/0! error if there is a no data in row 12 and the range G14:T41?

=(SUMIFS(G40:T40,G12:T12,"S",G11:T11,"AM"))/(SUMIFS(G41:T41,G12:T12,"S",G11:T11,"AM"))

 

[BrerRabbit]

Can you send an example of what you are dealing with?

 

[Juddman79]

Can you send an example of what you are dealing with?

Thanks, here is a link to the sheet.

Test.xlsx

Sheet1 Attendee Name,Date,1/1/2024,2/1/2024,3/1/2024,4/1/2024,5/1/2024,6/1/2024,7/1/2024 Day,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM Activity Type,SC,S,SC,S,S,SC,S,SC,SC,S,S,S,S Attendance,1,0,0,0,0,0,0,1,0,1,0,0,0,0 Person A,Y,Y,Y Pers...

docs.google.com

 

[BrerRabbit]

Thanks, here is a link to the sheet.

Test.xlsx

Sheet1 Attendee Name,Date,1/1/2024,2/1/2024,3/1/2024,4/1/2024,5/1/2024,6/1/2024,7/1/2024 Day,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM Activity Type,SC,S,SC,S,S,SC,S,SC,SC,S,S,S,S Attendance,1,0,0,0,0,0,0,1,0,1,0,0,0,0 Person A,Y,Y,Y Pers...

docs.google.com

You can't divide by zero. On the sheet you've sent, there's no class for S in the AM. Hence the zero and the divide by zero, which can't be done. SO I would just "wrap the formula in an IfError function. Do all 4 from W13 to Z13. The formulas will look like this:

=IFERROR((SUMIFS(G40:T40,G12:T12,"S",G11:T11,"AM"))/(SUMIFS(G41:T41,G12:T12,"S",G11:T11,"AM")),"No attendance in the AM slot")

"No attendance in the AM slot" can say anything you want, or nothing at all. Leave it as a "" if you wish.

 

[BrerRabbit]

Thanks, here is a link to the sheet.

Test.xlsx

Sheet1 Attendee Name,Date,1/1/2024,2/1/2024,3/1/2024,4/1/2024,5/1/2024,6/1/2024,7/1/2024 Day,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM,AM,PM Activity Type,SC,S,SC,S,S,SC,S,SC,SC,S,S,S,S Attendance,1,0,0,0,0,0,0,1,0,1,0,0,0,0 Person A,Y,Y,Y Pers...

docs.google.com

Actually, I've thought of a better solution. Follow these steps:
1. Row 40 is the summation of actual ("Y") in the various columns ie G40 = COUNTIF(G14:G38,"Y")
2. Row 41 is the summation of possible ("*") in the various columns ie G41` = COUNTIF(G14:G38,"*")
3. Row 42 is the calculation of percentages for each column: G42 = IFERROR(G40/G41,"") wrapped in the iferror
4. For W14, W14=AVERAGEIFS(G43:T43,G11:T11,"AM",G12:T12,"S")

Is that more like what you need?

 

[Juddman79]

Actually, I've thought of a better solution. Follow these steps:
1. Row 40 is the summation of actual ("Y") in the various columns ie G40 = COUNTIF(G14:G38,"Y")
2. Row 41 is the summation of possible ("*") in the various columns ie G41` = COUNTIF(G14:G38,"*")
3. Row 42 is the calculation of percentages for each column: G42 = IFERROR(G40/G41,"") wrapped in the iferror
4. For W14, W14=AVERAGEIFS(G43:T43,G11:T11,"AM",G12:T12,"S")

Is that more like what you need?

Thank you ever so much for your help, everything works great.

 

[BrerRabbit]

Thank you ever so much for your help, everything works great.

You're welcome. Just remember to tick the heart symbol on the left for your preferred answer if you feel that your question has been answered.