SUMIFS on a Multi Select Column

Say I'm running an event in November and I want to know the combined Target Attendees for both Sony and Panasonic. Now manually I can see that I've got 50 for Sony and 48 for Panasonic but if I wanted a formula to total this, how would it look? Primary Product is a multi-select drop down column.

I was thinking of a HAS with an AND function but can't seem to find an exact solution.

In doing the below I just get a result of 50 so it's only looking up the first part of the OR function.

=SUMIFS({Target Attendees}, {Month}, $Month@row, {Primary Product}, OR(@cell = "Sony", @cell = "Panasonic"))

Using HAS just gives me a 0.

=SUMIFS({Target Attendees}, {Month}, $Month@row, {Primary Product}, HAS(@cell, "Sony", "Panasonic"))

Where am I going wrong?πŸ€”

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Samantha McDonald,

    Something like this should work for what you're after:

    =SUMIFS({Target Attendees}, {Month}, $Month@row, {Primary Product}, OR(HAS(@cell = "Sony"),HAS(@cell = "Panasonic")))

    Any problems/questions then just post! πŸ™‚

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!