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

Hi @Joy Kwan
You're correct: you can either add together different SUMIFS to find each SUM perindividualtask (which may end up with duplicates), or you can create a SUMIFS formula that will look for certain combinations only, but it won't find the individual task on its own.
You could write into your formula all the possible combinations of selections and minus that off of your total, but this may get too complex, depending on how many criteria you have. Another option would be to SUM if the Task Type column does not have a specific selection, would that work for you?
Ex:
=SUMIFS([columns2]:[column2], [task type]:[task type], NOT(HAS(@cell, "N/A")))
Answers

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

Oh.... wait... you're doing multiselect.... 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!

Hi @Joy Kwan
You're correct: you can either add together different SUMIFS to find each SUM perindividualtask (which may end up with duplicates), or you can create a SUMIFS formula that will look for certain combinations only, but it won't find the individual task on its own.
You could write into your formula all the possible combinations of selections and minus that off of your total, but this may get too complex, depending on how many criteria you have. Another option would be to SUM if the Task Type column does not have a specific selection, would that work for you?
Ex:
=SUMIFS([columns2]:[column2], [task type]:[task type], NOT(HAS(@cell, "N/A")))

Thank you! I will try the different what you have suggested.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!