Multiple SUMIFS

[wdgor]

I have multiple tabs on a workbook. There can be multiple comma delimited reference points in each row in a cell on the "Client" tab that need to be summed from the "Cohort" tab (see cell B2 below). I use the following formula to separate out the reference points:
=IF(B2="","",TRIM(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>","//s")))). While this works fine, it will span 50 columns for some cells (depending on how many reference points are associated with that row, i.e. a variable number of points).

I then apply the following formula to sum all the reference points from the "Cohort" tab:
=IF(B2="",0,SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,C2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,D2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,E2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,F2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,G2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,H2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,I2)+(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,J2)))))))))

While this all works fine, it is very sloppy, i.e. I would need to have 50 SUMIFS to get the job done. I believe there has to be a better way. I am hoping that the experts who visit this site can provide assistance. Thank you in advance for any insights.

C02389,C02391,C02396,C02422,C02394,C02397,C02398,C02395,C02399,C02400,C02401,C02423,C02402,C02403,C02409,C02410,C02409,C02410,C02411,C02412,C02413,C02413,C02414,C02425,C02384,C02384,C02415,C02393,C02408,C02424,C02351,C02404,C02405,C02416,C02406,C02407,C02420,C03820,C02417,C02418,C02000,C02385,C02386,C02387,C02388,C07542,C07541,C02349,C02419,C02420

 

[wdgor]

I Googled it and it looks like it still splits the values in B2 over the 50 columns.

 

[wdgor]

OMG - I just tried your updated formula, deleted the 50 "extra" columns and it worked PERFECTLY!! Thank you very much. Someday, I hope to be a better master of Excel but it is great to have this Board to get assistance from the true Excel geniuses. Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.