Sum dollars based on names in multiple columns

Vincent Carranza

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

Andrée Starå

Hi @Vincent Carranza

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!

Mike TV

@Vincent Carranza

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

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

Vincent Carranza

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

Mike TV

@Vincent Carranza

I was afraid of that. It's your [Coach 1]:[Coach 5] range. It doesn't work with a range of 5 whole columns.

Vincent Carranza

@Mike TV

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

Andrée Starå

Hi @Vincent Carranza

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!

Vincent Carranza

@Andrée Starå That worked! Thank you!

Andrée Starå

@Vincent Carranza

Excellent!

Happy to help!