Skrej
Board Regular
- Joined
- May 31, 2013
- Messages
- 159
- Office Version
- 365
- Platform
- Windows
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.
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.
