# 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])

Tags:

• ✭✭✭✭✭✭

I hope you're well and safe!

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.

• ✭✭✭✭✭✭

=SUMIFS([\$ per Split]:[\$ per Split], [Coach 1]:[Coach 5], CONTAINS("Skye ", @cell))

Is there a reason there's a space after Skye?

• edited 12/31/22

@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.

• edited 12/31/22

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!

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!

• ✭✭✭✭✭✭

Excellent!

Happy to help!

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!