SUMIFS on a Multi Select Column

Samantha McDonald

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?🤔

Nick Korna

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! 🙂

Nick Korna

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! 🙂

Samantha McDonald

Thanks Nick, it didn't work with the = sign but a comma fixed it!

Have a great day!

=SUMIFS({TARGET HCP ATTENDEES}, {MONTH}, $MONTH@row, {PRODUCTS OTHER}, OR(HAS(@cell, "Sony"), HAS(@cell, "Panasonic")))