Sum dollars based on names in multiple columns
I have a shared tips sheet. A big tip is divided by the coaches that are coaching for the group. If John is coach 1, Dave is coach 2, and
Skye is coach 3, then the $30 tip would be split $10 each. The next group may have Skye , Mike, and Nate and may have a $60 tip. Etc.
I am trying to write a formula that says of Skye is in any of the coaches columns, sum the split tip amount.
I have tried a few different formulas but have not been successful. I am hoping someone has the answer.
=SUMIFS([$ per Split]:[$ per Split], [Coach 1]:[Coach 5], CONTAINS("Skye "))
=IF(CONTAINS("Skye ", [Coach 1]:[Coach 5]), SUM([$ per Split]:[$ per Split]),)
=SUMIF([Coach 1]:[Coach 5], "Skye", [$ per Split]:[$ per Split])
Best Answer
-
I hope you're well and safe!
To add to Mike's excellent advice/answer.
The ranges must match, so one can't be one column and the other six.
Try something like this. (Replace Skye for the other coaches)
=SUMIFS([$ per Split]:[$ per Split], [Coach 1]:[Coach 1], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 2]:[Coach 2], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 3]:[Coach 3], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 4]:[Coach 4], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 5]:[Coach 5], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 6]:[Coach 6], "Skye")
Would that work/help?
I hope that helps!
Have a fantastic weekend & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
=SUMIFS([$ per Split]:[$ per Split], [Coach 1]:[Coach 5], CONTAINS("Skye ", @cell))
Is there a reason there's a space after Skye?
-
@Mike TV That is one of the many I tried.
When I copy and paste your formula, it comes back in the sheet summary as "#INCORRECT ARGUMENT SET". I also tried it by removing the extra space after Skye.
I also rebuilt the formula and it came back #UNPARSEABLE.
=SUMIFS([$ per Split]:[$ per Split], [Coach 1]:[Coach 5], CONTAINS("Skye", @Cell))
-
I was afraid of that. It's your [Coach 1]:[Coach 5] range. It doesn't work with a range of 5 whole columns.
-
It won't even do a range of 2. I just tried it.
I currently have a work around where I am using =SUMIF([Coach 1]:[Coach 1], "Skye", [$ per Split]:[$ per Split]) as a sheet summary and changing the range for Coach 2 through Coach 5 and then I am adding those together in a sheet summary formula. It's just a lot to build for each of the 12 coaches and more of a pain when we have turnover.
=[Skye Coach 1]# + [Skye Coach 2]# + [Skye Coach 3]# + [Skye Coach 4]# + [Skye Coach]#
-
I hope you're well and safe!
To add to Mike's excellent advice/answer.
The ranges must match, so one can't be one column and the other six.
Try something like this. (Replace Skye for the other coaches)
=SUMIFS([$ per Split]:[$ per Split], [Coach 1]:[Coach 1], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 2]:[Coach 2], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 3]:[Coach 3], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 4]:[Coach 4], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 5]:[Coach 5], "Skye") + SUMIFS([$ per Split]:[$ per Split], [Coach 6]:[Coach 6], "Skye")
Would that work/help?
I hope that helps!
Have a fantastic weekend & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå That worked! Thank you!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!