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]

Okay, I found how to search for all instances of code beginning with 't', but the question remains how to add in all instances of code beginning with 'e'

=LET(d,VSTACK('Aug:oct2'!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"))))))

 

[hagia_sofia]

Try to replace the (LEFT(d)="T") part with:

((LEFT(d)="T")+(--(LEFT(d)="E"))

 

[Skrej]

That did it, thank you.

 

[Skrej]

The only hitch is that if the name (listed in Col C) begins with a 't' or 'e', then it adds one to the code count. It doesn't do that when searching for only one code letter, though.

 

[hagia_sofia]

Could you please post a sample of the data?

 

[Skrej]

I realize this is less than ideal, but all I can do is post some screenshots. I don't have admin permissions to install the XL2bb add on to post the actual code.

Each shot is one month. Edgar has 8 t/e combinations on each month, but the tallies using your formula count 9 for each month. Names not beginning with 'E' or 'T' (i.e. Babs) shows the correct count..

 

[hagia_sofia]

Many thanks! My guess is that this is due to the fact that it counts Edgar into the equation, i.e. that those names are part of the array; to test that, please return to your original formula and replace (LEFT(d)="T") with

(ISNUMBER(--(RIGHT(d))))

 

[Skrej]

=LET(d,VSTACK(Aug!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))))))

Yes, that counts every time the letter 't' comes at the beginning of a cell, including the name.

'

Here's the tally if I add a 't' in front of 'Matt'

 

[hagia_sofia]

Thanks for the feedback. Would this count correctly then:

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