Sumifs with Multi Select DropDowns
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
Answers

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!

@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.
Senior Program Coordinator
De Anza College

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))

@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
Senior Program Coordinator
De Anza College

@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 OctoberDecember, 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?
Senior Program Coordinator
De Anza College

@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))

@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)))
Senior Program Coordinator
De Anza College

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.

@Paul Newcome Oh geez 🤦♀️ that worked!!!!
Thanks for catching that.
Senior Program Coordinator
De Anza College
Help Article Resources
Categories
Check out the Formula Handbook template!