Sum If Multi Drop Down Contains @Cell

Travis Ryan

I am struggling to get a formula to work correctly. I have a "Crew" Column which is a multi select contact column. I want the crew to be able to select multiple members and enter a single "Hours" for the day. For example Antonio and Tacho work the same job for 10 hours. Then to have each persons total hours for the project populate in the total hours section. I can get it to work just fine with a single person. As soon as I select multiple it breaks.

It should be showing 20 hours each for person under column 11 under Total Hours

This works with only one selection in dropdown =SUMIF(Crew:Crew, ProductionCrew@row, Hours:Hours)

This doesn't work at all =SUMIF(Crew:Crew, CONTAINS(ProductionCrew@row, Crew:Crew), Hours:Hours)

Any help appreciated

Travis Ryan

https://community.smartsheet.com/discussion/comment/317492#Comment_317492

I tried replacing the formula as you mentioned. I don't get an error but the number in total hours just stays at zero. What would @cell would I be specifying? Perhaps I used it incorrectly?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317496#Comment_317496

The formula works... the problem is trying to use CONTAINS against Contact type columns. It just doesn't work with them.

I tried using it against multi-select Contact columns like yours and got the same errors, no matter what I tried. When I created Crew and ProductionCrew columns with simple multi-select list of names, it worked perfectly!

Jeff Reisman

Try adding the @cell specifier in place of listing the range a second time:

=SUMIF(Crew:Crew, CONTAINS(ProductionCrew@row, @cell), Hours:Hours)

Travis Ryan

https://community.smartsheet.com/discussion/comment/317492#Comment_317492

I tried replacing the formula as you mentioned. I don't get an error but the number in total hours just stays at zero. What would @cell would I be specifying? Perhaps I used it incorrectly?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317496#Comment_317496

The formula works... the problem is trying to use CONTAINS against Contact type columns. It just doesn't work with them.

I tried using it against multi-select Contact columns like yours and got the same errors, no matter what I tried. When I created Crew and ProductionCrew columns with simple multi-select list of names, it worked perfectly!

Travis Ryan

Thank You! It figures I cant use the contacts for it. Works fine with the non contact list.