Sumifs with Multi Select DropDowns

Nick Horton

Hi all, this is very simple, but I am pulling my hair out!

I am trying to do a SumIfs, where one of the criteria is a multi select dropdown.

My first attempt is

=sumifs({column to be totaled}, {Multi select Column}, Option 1)

If I do this, then it only Suns when Option 1 is the only option, not where option 1 and and any other option are selected.

I have tried variants of contain and Has but I keep getting errors.

Essentially I have 5 options, but this would be about 25 variations.

I would like to Sum if option one is included regardless of what else is added, likewise with option 2 etc.

Clear as mud? Thanks in advance for any help!

Cheers

Nick

David Joyeuse

Hi @Nick Horton

HAS should be working here.

=SUMIFS({column to be totaled}, {Multi select Column}, HAS(@cell, "Option 1"))

If that does not work try again using FIND:

=SUMIFS({column to be totaled}, {Multi select Column}, FIND("Option 1", @cell)>0)

Hope it helped!

Stacey Carrasco

@David Joyeuse But what if I have multiple criteria to FIND not just one. How can I include a list of items to FIND and SUM if any of the criteria is found?

I have the first part of the formula as you've noted above correctly working but I receive invalid operation when I add another FIND criteria (FIND("Option 2", @cell)>0) to the formula and that's where I get stumped.

David Joyeuse

@Stacey Carrasco

Depends on how you want it to operate. Are you looking to SUM when both options are found, or do you want to sum it if it's either one of them.

Depending on this answer you'd rather use an OR or AND function within the SUMIFS.

=SUMIFS({column to be totaled}, {Multi select Column}, OR(FIND("Option 1", @cell)>0, FIND("Option 2", @cell)>0))

=SUMIFS({column to be totaled}, {Multi select Column}, AND(FIND("Option 1", @cell)>0, FIND("Option 2", @cell)>0))

Stacey Carrasco

@David Joyeuse Yes, SUM when all options are found. I'll check out the formula more closely however right now I'm receiving an Invalid data type error message

Thanks

Stacey Carrasco

@Paul Newcome I'm stumped this formula I've written is receiving INVALID DATA TYPE so I'm missing something. I'm trying to SUM up completions numbers only if Course Name is found and the END Date is between October-December, 2020

=SUMIFS({Completion}, {Course Name}, AND(FIND("Course1", @cell) > 0, AND(FIND("Course2", @cell) > 0, AND(FIND("Course3", @cell) > 0, AND(FIND("Course4", @cell) > 0, AND(FIND("Course5", @cell) > 0, AND(FIND("Course6", @cell) > 0, AND(FIND("Course7", @cell) > 0, {Course End Date}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 12, IFERROR(YEAR(@cell), 0) = 2020)))))))))

What am I missing here?

Paul Newcome

@Stacey Carrasco It is just the use of the AND statements. You only need one for multiple sets of criteria from the same range. But I do feel like you are really wanting to use an OR instead.

=SUMIFS({Completion}, {Course Name}, OR(FIND("Course1", @cell) > 0, FIND("Course2", @cell) > 0, FIND("Course3", @cell) > 0, FIND("Course4", @cell) > 0, FIND("Course5", @cell) > 0, FIND("Course6", @cell) > 0, FIND("Course7", @cell) > 0), {Course End Date}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 12, IFERROR(YEAR(@cell), 0) = 2020))

Stacey Carrasco

@Paul Newcome I'm still receiving an #INVALID DATA TYPE for my formula below🤔

=SUMIFS({Completion}, {Course Name}, OR(FIND("Course1", @cell) > 0, FIND("Course2", @cell) > 0, FIND("Course3", @cell) > 0, FIND("Course4", @cell) > 0, FIND("Course5", @cell) > 0, FIND("Course6", @cell) > 0, FIND("Course7", @cell) > 0, {Course End Date}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 12, IFERROR(YEAR(@cell), 0) = 2020)))

Paul Newcome

You aren't closing off the OR statement before you start your date range/criteria.

FIND("Course7", @cell) > 0), {Course End Date}

Then remove a closing parenthesis from the end of the formula.

Stacey Carrasco

@Paul Newcome Oh geez 🤦‍♀️ that worked!!!!

Thanks for catching that.