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

Tags:

Answers

  • David Joyeuse
    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
    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.

    Senior Program Coordinator

    De Anza College

  • David Joyeuse
    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
    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

    Senior Program Coordinator

    De Anza College

  • Stacey Carrasco
    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?

    Senior Program Coordinator

    De Anza College

  • Paul Newcome
    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
    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)))

    Senior Program Coordinator

    De Anza College

  • Paul Newcome
    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
    Stacey Carrasco ✭✭✭✭✭

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

    Thanks for catching that.

    Senior Program Coordinator

    De Anza College

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!