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]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Skrej]

I'm getting fits about installing the add-in due to my school's IT restrictions, which are severe. We can't even delete program icons off our desktop without submitting a ticket....

I may try it from my home computer later this evening.

 

[Fluff]

Ok, the other option is to just copy/paste a range of data.

 

[Skrej]

I just realized my approach is flawed anyway, since all it will do at best is list the name with five or more. I'll need another formula in the column beside to show the tally.

 

[Skrej]

Here's a screenshot. The cols and rows differ from my OP as I was trying to simply.

 

[Fluff]

OK, how about

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

Where C30 holds the type of code you want to check

 

[Skrej]

Thank you for that, which does work. However, rather than having to specifically reference a cell for a variable to check, I was hoping to just check for all instances of "A".

I may not have explained it well. What I'm trying to do is check how many times a given name (row) has "a", and if it has five or more, create the list and tell me how many. Essentially, run down the list of names, look how many absences they have (signified by code "A"), then list their name at the bottom if they have five or more.

However, I could just use your solution, and make C30 white text so it doesn't show when we print the roster. It's all locked and password protected anyway, so there's no chance anyone can accidentally overwrite C30.

 

[Fluff]

If you just want to check for A then you can use

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

 

[Skrej]

Great, thank you very much.

If I may ask one more follow-up. I'm trying to adapt this for a couple of the other codes we use besides just 'A'. A few of them are an alphanumeric code such as T15,T30, T45, etc.

I'm trying to adapt your solution as follows to include a wildcard for all instances of 'T' (t15,t30, etc.), but it's throwing an 'empy arrays not supported' error.

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