Modifying formula to include more options

[Skrej]

Hi,

I have the following formula (previously provided via another thread) which cumulative lists instances of a code for a given number of sheets.

=LET(d,VSTACK('Aug:Oct'!C8:V32),n,INDEX(d,,1),u,UNIQUE(FILTER(n,n<>"")),HSTACK(u,BYROW(u,LAMBDA(br,SUMPRODUCT((INDEX(d,,1)=br)*(d="t15"))))))

It works fine, and I understand how to modify it for given sheets. What I'd like to do is have it search for multiple codes (e.g. not just 't15' in the formula above, but also 't30, t45, t60 as well as 'e15', 'e30','e45' and 'e60'.

Is there I way I can set the value of d in the code above to include multiple options? I've tried adding the other codes separated by commas, but that throws an error.


(d="t15","t30)
(d="t15", d="t30")

Not sure if I'm just missing some syntax, or if I need to nest multiple index statement for each code, or what.

To further clarify, each month's sheet has a list of names down the leftmost column, followed by a code for each day (1 day per subsequent column to the right of the names). Other codes are used, but I don't need to tally them.

My main problem is I don't understand the formula well enough to interpret it.

 

[Skrej]

That seems to work although for some reason it adds quite a bit of unwanted space above the list of names.

 

[Skrej]

I'm embarrassed to say the spacing was just a row width that I was unaware was enlarged. Thank you for all your help.

If possible, I'd like to exclude instances of zero (if the value is zero, don't show the name), but I can live with this. Again, thanks for the help.

 

[hagia_sofia]

Hello again, thanks for your patience! Do you mean this:

=LET(
d,VSTACK(Aug!C8:V32),
n,INDEX(d,,1),
u,UNIQUE(FILTER(n,n<>"")),
a,HSTACK(u,BYROW(u,LAMBDA(br,SUMPRODUCT((INDEX(d,,1)=br)*(ISNUMBER(--(RIGHT(d)))))))),
FILTER(a,CHOOSECOLS(a,2)<>0))

 

[Skrej]

Yes, that works well. Thank you very much! Due to space on the sheet (which we print out) and the potential number of students, it's preferable not including names with zero tallies.

I really appreciate all your time and effort. Once I've finished and shared the master version, around a dozen people will ultimately benefit from your contribution.

 

[hagia_sofia]

That is good to hear and thanks for your kind words...