I am trying to sum values based that equal a lookup value. However, that value is actually a list of values delimited by a comma. Below is an example of what I mean.
Suppose I have raw data in the form of sheet1 below:
Sheet1:
| A | B | |
|---|---|---|
| 1 | ID | VALUE |
| 2 | A | 30 |
| 3 | A | 50 |
| 4 | A | 20 |
| 5 | B | 10 |
| 6 | B | 20 |
| 7 | C | 70 |
| 8 | C | 40 |
| 9 | D | 30 |
| 10 | E | 50 |
| 11 | F | 20 |
| 12 | F | 30 |
| 13 | G | 10 |
And I have a look table that groups all IDs by their respective teams, as per sheet2 below.
Sheet2:
| A | B | |
|---|---|---|
| 1 | TEAM | IDS |
| 2 | Red | A, B |
| 3 | Blue | C, D |
| 4 | Green | E, F, G |
And I want to create a report where the user can select the team name, and the sum of the values in sheet1 will aggregate based on the selection, as per the following example. So the user would select "Green" in cell B1 and it would return the sum of values that correspond to E, F, and G in sheet1.
Report:
| A | B | |
|---|---|---|
| 1 | Select Team: | Green |
| 2 | Sum: | 110 |
I have searched all over for a solution to this and was able to find something similar. I tried to repurpose the formula for my data but couldn't get it to work because I think that solution dealt with numbers rather than text.
Note, this only works with Excel 2013 or later and only on PC. FILTERXML is not available on Mac or prior to 2013.
If Mac or prior to 2013:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,TRIM(MID(SUBSTITUTE(VLOOKUP(F1,Sheet2!A:B,2,FALSE),",",REPT(" ",999)),(ROW($ZY1:INDEX($ZY:$ZY,LEN(VLOOKUP(F1,Sheet2!A:B,2,FALSE))-LEN(SUBSTITUTE(VLOOKUP(F1,Sheet2!A:B,2,FALSE),",","")) 1))-1)*999 1,999))))
