Sumifs for Multi Select Drop Down

I have a column that is setup as multi select. I would like use sumifs but only sum the 1 item from the multi select. Do I use an OR statement? For example: column 1 is called task type, 1 row has outdoor, indoor, flooring; column 2 is called hours worked and the value is 4. The calculation would only add 4 not 4+4+4=12. If I use the HAS statement it would return 12.

Best Answer

Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    Your SUMIFS range should be your Column 2/Hours Column.... then the conditions are the other range.


    This sums up the hours when the task says outdoor.....

    =SUMIFS([Column2]:[Column2], [Task Type]:[Task Type], "outdoor")

    Change the segment of the formula that has "outdoor" to calculate the other select options.

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    Oh.... wait... you're doing multi-select.... derp.


    Yeah you want to include the HAS....


    = SUMIFS([Column2]:[Column2], HAS([Task Type]:[Task Type], "outdoor"))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • If I use HAS and I have a sumifs statement is the following, wouldn't it sum the values each time instead of once?

    =sumifs([columns2]:[column2],Has([task type]:[task type],"outdoor")+sumifs([columns2]:[column2],Has([task type]:[task type],"indoor")+sumifs([columns2]:[column2],Has([task type]:[task type],"flooring")

    I only want to sum if once to be the value of 4 not 12. I wasn't sure if an OR statement would work. Maybe you can provide an suggestion.

    Thanks!

  • Thank you! I will try the different what you have suggested.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!