All instances of a value tied to a specific entry across multiple sheets

[Skrej]

I have multiple sheets, one for each month, named Jan, Feb, Mar, etc. On each sheet in Col A is a list of names. Some of those names repeat over various months, while some drop off the list and new names are added. The names are always in Col 1, but the number of rows with names in Col A is dynamic (although a maximum of about 30), and since the list is alphabetized, the same name may not necessarily appear in the same row each month. However, names always start in A3.

Col B-M contain a dropdown list which allows only three values ('P', 'A', or "E").

On a separate "Summary" page, I would like to count all instances across all months that each given name has an entry of 'A". For example, if Bob has 3 'A' entries in Jan, two 'A' in Feb, and 4 in Mar, but drops off the list in Apr, his name has count of 9. Let's say the list of names on 'Summary' starts in A3, with the corresponding count starting in B3.

I know how to gather a unique list of names, =UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,Jan!A1:A100,Feb!A1:A100,Mar!A1:A100)&"</B></A>","//B")) (I shortened the formula to just three months for brevity), but I'm uncertain how to index and match the names and 'A' count across sheets when 'Bob' may appear in different rows from month to month.

I suspect this is some combination of vlookup and Index with Match, but I could use some help stringing it together.

 

[Fluff]

How about

=LET(d,VSTACK(Jan:Dec!A3:M50),n,INDEX(d,,1),u,UNIQUE(FILTER(n,n<>"")),HSTACK(u,BYROW(u,LAMBDA(br,SUMPRODUCT((INDEX(d,,1)=br)*(d="A"))))))

 

[Skrej]

Thank you, that seems to do the trick. It's a bit beyond my understanding, but at least I can figure out how to adapt ranges if I add more columns.

If someone uses an older version of Excel, would these still work?

 

[Fluff]

No, that will only work in 365

 

[Skrej]

Is there a way to retro proof it? I need to share the sheet with users who may or may not be using 365, at least back a few versions?

 

[Fluff]

The only way I can think of doing it would be to use a sumproduct for each & every sheet.
But the formula you posted will only work with 2021 & 365

 

[Skrej]

Hmm. I will play with that. With only twelve maximum sheets, that might not prove too unwieldy. I might be able to do some of the calculations on a hidden sheet or something to keep it tidier.

I thought about VBA but ruled that out since so many institutions have IT restrictions on running macros and such.

 

[Fluff]

Glad to help & thanks for the feedback.