Creating list of all instances where sum of occurrences is greater than a certain number.

[Skrej]

I have an attendance sheet where instructors enter various codes from a drop down list. One of the codes is "A" for an absence.


Sheet is set up with class roster starting in C2, with Cols D-V being the daily attendance columns (sheet is for a month at a time). Col headers D-V contain the dates.

What I'd like to do is have the sheet automatically list all names who have five or more absences, along with the total number of absences for that student somewhere at the bottom, say starting in C30.

Here's what I have so far, planning to copy it down the respective number of rows starting in about C30.


However, it's not indexing the names, instead just leaving a blank.

=IFERROR(INDEX($C$2:$C$25,SMALL(IF($D$2:$J$2=(IF(COUNTIF(D2:J2,"a")>4,COUNTIF(D2:J2,"a"),"")),ROW($D$2:$D$12)),ROW(1:1))-1,1),"")

I'd appreciate if somebody could either point me in the right direction, or suggest a better way of doing it. Rather than listing all names and a total of absences, I'd ideally only like it to list those with five or more absences. Anyone names with less than five won't appear on the list.

As mentioned, names start in C2 running down to C25, with attendance codes in Col D-V.

 

[Fluff]

Try

=LET(b,BYROW(D8:V25,LAMBDA(br,SUM(--(left(br)="T")))),FILTER(HSTACK(C8:C25,b),b>=5))

 

[Skrej]

Thank you, once again. Much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Skrej]

Apologies for reviving a dead thread, but since it's a related question, I thought I'd resurrect this one instead of a new one.

My needs have changed a bit, and while this solution still works well for individual month tallies, I'd now like to have it do the same for several months (i.e. several sheets). See this thread for a similar issue. That solution however searched only for specific instances of 'A', while I'd like to search for all instances of 'E' as per above, just over multiple sheets.

If possible, I'd like to combine all instances of 'E' and all instances of 'T'. Screenshot in post #6 of this thread still applies.

The following formula nearly does it, except if the name in col C starts with 't' or 'e', then it adds one to the count. As per screenshot in post #6, codes start in Col D.

=LET(d,VSTACK(Aug:Sep!C8:V32),n,INDEX(d,,1),u,UNIQUE(FILTER(n,n<>"")),HSTACK(u,BYROW(u,LAMBDA(br,SUMPRODUCT((INDEX(d,,1)=br)*((LEFT(d)="T")+(--(LEFT(d)="E"))))))))

This formula successfully searches for all instances of codes with 'e' or 't', but only for the given page.

=LET(b,BYROW(D8:V32,LAMBDA(br,SUM(--(LEFT(br)="T")+(LEFT(br)="e")))),FILTER(HSTACK(C8:C32,b),b>0))

 

[Skrej]

Attempts such as the following to add in page names throw a value error (wrong data type)

=LET(b,BYROW(Aug:Sep!D8:V32,LAMBDA(br,SUM(--(LEFT(br)="T")+(LEFT(br)="e")))),FILTER(HSTACK(Aug:Sep!C8:C32,b),b>0))

 

[Fluff]

As you already have a thread for this new question, you need to stick to that.
Thanks