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

 

[DanteAmor]

I then apply the following formula to sum all the reference points from the "Cohort" tab:

Try this:

=IF(B2="",0,SUMPRODUCT(((Cohort!$E:$E=C2)+(Cohort!$E:$E=D2)+(Cohort!$E:$E=F2)+(Cohort!$E:$E=G2)+(Cohort!$E:$E=H2))*(Cohort!$BU:$BU)))

If you create a named range for column E of the "Cohort" sheet, for example with the name "_E", the formula could be:

=IF(B2="",0,SUMPRODUCT(((_E=C2)+(_E=D2)+(_E=F2)+(_E=G2)+(_E=H2))*(Cohort!$BU:$BU)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that you are using 365 or 2021 & the formula in C2 is a spill range try

=IF(B2="",0,SUM(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,C2#))

 

[wdgor]

Dante - your formula worked but I would need 50 iterations of (_E=H2, I2, J2, etc.) to encompass all the columns. Thanks for the thought.

Fluff - I updated my profile and am using 365.

Your formula worked perfectly and was independent or the number of columns - nice! You are right on the spill issue. Is there a way to not have to spread out all the values in B2 over 50 columns, i.e. somehow parse the value in B2 and then do the SUM? There are over 1500 rows and future entries may go over the 50 columns and it would be great if I didn't need to adjust the number of columns to accommodate the number of elements in B2. Thanks.

 

[Fluff]

Not sure what you mean by

it would be great if I didn't need to adjust the number of columns

There should be nothing to adjust.

 

[wdgor]

The way that I currently have it set up, B2 contains the comma delimited reference points that are used to sum up the corresponding values from the Cohort tab. The only way I could figure out how to look them up was to use: =IF(B2="","",TRIM(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>","//s")))) to spread out the values over the 50 columns (some have only 1 value in B2 and some have 2, 3 etc. up to 50). I was wondering if there was a way to not have to spread out the values over all those columns and somehow parse the string in B2 and use that to do the SUM. Thanks.

 

[Fluff]

Do you have the textsplit function?

 

[wdgor]

Yes, but I have never used it.

 

[wdgor]

I just tried playing with it and got a #SPILL error. I'm sure it is because I don't know what I am doing.

 

[Fluff]

In that case try

=IF(B2="",0,SUM(SUMIFS(Cohort!$BU:$BU,Cohort!$E:$E,TEXTSPLIT(B2,","))))