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 per-individual-task (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 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!
-
Hi @Joy Kwan
You're correct: you can either add together different SUMIFS to find each SUM per-individual-task (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
Check out the Formula Handbook template!
