Using multi-select dropdown list as criteria for Index/Match Lookup with SUMIFS

Elizabeth N

Hi all — Looking for some guidance on the below…

I have a sheet named "Tracker" with a dropdown column of job levels (named "Target Audience") and another column that I am wanting to sum the job levels from whatever is selected for that row ("Total Target"). The totals for each job level are pulling from as a cross sheet reference from another sheet.

If only one job level is selected, the below formula works:

=INDEX({Total Count}, MATCH([Target Audience]@row, {Job Level}, 0))

If the user selects more than one job level, however, I cannot get the SUMIF formula to work. Currently I have it as the below:

=SUMIFS({Total Count}, {Job Level}, CONTAINS(@cell, INDEX ({Total Count}, MATCH([Target Audience]@row, {Job Level}, 0))))

This formula is returning "#NO MATCH"

In the below screenshot, the first two rows are set without the SUMIF formula to show that the Index/Match is working. The third row that has two selections for Target Audience is where #NOMATCH is being returned.

Can anyone help me determine what I am missing? Thank you!

Paul Newcome

Give this a try:

=SUMIFS({Total Count}, {Job Level}, HAS([Target Audience]@row, @cell))

Kerry St. Thomas

It appears that what you're trying to do is Index/Match P1 and P2 to another chart somewhere. The problem is you are trying to match "P1 P2" to your other - Smartsheet isn't seeing that cell as "Match P1 or P2" but is combining them. Without testing the formula, it feels like the @cell in your formula needs to be parsed out into component parts, depending on how many options there are in your Target Audience dropdown.

If there are only a few dropdowns, you could insert a few helper columns - one to return your SUMIFS formula ONLY if the item has P1 in it, one to return ONLY if the Audience has P2 in it, one if it has P3, and so on - and then have Total Target add all those formula values together. (This gets unscalable if you have like a whole bunch of options in your target audience though.)

I'm sure there's a more elegant solution, but beyond either a big ol' nested IF or the helper column option, I really got nothin. I try to avoid multi-select dropdowns for this reason. Good luck!

Paul Newcome

What does your source data look like?

Elizabeth N

Hi @Paul Newcome — thanks for jumping in. Below is a screenshot of the Source Sheet (titled "Job Levels"). The total counts are formulas linked to another sheet.

Paul Newcome

Give this a try:

=SUMIFS({Total Count}, {Job Level}, HAS([Target Audience]@row, @cell))

Elizabeth N

@Paul Newcome - YOU ARE INCREDIBLE! That worked. Thank you so, so much. I really appreciate it :)

Paul Newcome

Happy to help. 👍️