Multiple part problem....easiest first

[lakersfankb81]

I think this is the easier part of my problem but basically I have a group of about 5-6 people working for me in a bar where tips are pooled between some people, and others get a percentage of different sales. Below is a very simplified version of the end result I get after calculating what goes to who. I have about 2 weeks worth of transfers that I type in by day first, with about 5-6 transfers each, so I have about 70-80 of these at the end. Firstly, I'm looking for a formula to total the numbers from the last column so that in another area I can have all money going from Joe to Billy, Joe to Steve, Dave to Billy, etc, etc without having to manually go through and find each spot where the names occur. Is there some way to do this??[TABLE="width: 500"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]Billy[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Billy[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Steve[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]Billy[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Billy[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Billy[/TD]
[TD]Steve[/TD]
[TD]$10[/TD]
[/TR]
</tbody>[/TABLE]

 

[j_unsuitable]

Hey,

you could use SUMIFS:

	A	B	C
Joe	Billy
Steve	Billy
Joe	Steve
Dave	Billy
Joe	Billy
Billy	Steve
Joe	Billy

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]15[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C8[/TH]
[TD="align: left"]=SUMIFS($C$1:$C$6,$A$1:$A$6,A8,$B$1:$B$6,B8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Or use a Pivot Table:

Excel 2007

	E	F	G	H	I	J	K	L
Sum of Money	Sum of Money
Billy	Dave	Billy	Steve	Grand Total
Joe	Billy
Steve	Dave
Billy Total	Joe
Steve	Billy	Steve
Joe	Grand Total
Steve Total
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]gets from
[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Julian

 

[lakersfankb81]

Awesome thank you! The pivot table seems a bit easier to set up but it didn't look like it updated if I changed anything. Not a big deal as I can just run it after everything else is squared away.
The next part is a little difficult I think and may not even be worth the trouble.
I would love to just take my existing figures and put them in for each employee who works, then have the tipping amounts be auto-filled in to the sheet that I used previously somehow.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[TD]Food Sales[/TD]
[TD]Charge Tips[/TD]
[/TR]
[TR]
[TD]4/19[/TD]
[TD]Joe[/TD]
[TD]100[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Billy[/TD]
[TD]200[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Steve[/TD]
[TD]50[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Charge tips would be split evenly so they would each get 20, but would I have to manually figure out where to take the 10 from?
The food sales would all be combined, take 5% or so and each of those employees would give an equal share of that 5% to Dave for running food/bussing.
I hope that somewhat makes sense?

Thanks for your help!

 

[j_unsuitable]

In the PivotTableTools Ribbon there is a refresh button.

But it seems you started kinda backwards, so you dont have the data from your first post yet?
And it will probably never look even close to the data from post 1, right? Cause the differences in Tips will never lign up that exactly, like one person will never be exactly tipped below average as much as someone will be tipped above average.
Wouldnt it be easiest if you collect the tips, subtract the money owed to the busboy and distribute the rest in equal shares to the workers? (Option 2 in the example below)
But I have never worked in the service industry so i have no clue if thats allowed
Another option would be with like a credit system , in which theyd all pay the busboy his/her share and then have a credit or debit from their share of the tips which someone would have to collect/distribute (option 1), or you make the busboys share part of that credit system (option 1b)

	A	B	C	D	E	F	G	H	I
Date	Employee	Food Sales	Charge Tips	Tips Average	Credit f. Tips	Money owed to busboy	New credit	Their Share of tips after busboy is payed
Joe
Billy
Steve
Mike
Joe
Billy
Billy
Option 1

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01,04,2016
[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]25,75[/TD]
[TD="align: right"]8,75[/TD]
[TD="align: right"]5,63[/TD]
[TD="align: right"]3,12[/TD]
[TD="align: right"]20,12[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01,04,2016
[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]25,75[/TD]
[TD="align: right"]-10,25[/TD]
[TD="align: right"]5,63[/TD]
[TD="align: right"]-15,9[/TD]
[TD="align: right"]20,12[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01,04,2016
[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]25,75[/TD]
[TD="align: right"]-6,25[/TD]
[TD="align: right"]5,63[/TD]
[TD="align: right"]-11,9[/TD]
[TD="align: right"]20,12[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01,04,2016
[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25,75[/TD]
[TD="align: right"]7,75[/TD]
[TD="align: right"]5,63[/TD]
[TD="align: right"]2,12[/TD]
[TD="align: right"]20,12[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]02,04,2016
[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7,5[/TD]
[TD="align: right"]2,5[/TD]
[TD="align: right"]22,5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]02,04,2016
[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-10
[/TD]
[TD="align: right"]7,5[/TD]
[TD="align: right"]-17,5[/TD]
[TD="align: right"]22,5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]03,04,2016
[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7,5[/TD]
[TD="align: right"]-7,5[/TD]
[TD="align: right"]2,5[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Option 1b
[/TD]
[TD="align: center"]Option 1b
[/TD]
[TD="align: center"]Option 2[/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E2[/TH]
[TD="align: left"]=AVERAGEIFS($D$2:$D$8,$A$2:$A$8,A2)
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F2[/TH]
[TD="align: left"]=E2-D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G2[/TH]
[TD="align: left"]=ROUND((SUMIFS(C$2:C$8,A$2:A$8,A2)*0.05)/COUNTIF($A$2:$A$8,A2),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]H2[/TH]
[TD="align: left"]=F2-G2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I2[/TH]
[TD="align: left"]=E2-G2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

But I have a feeling I am making this way more complicated than it needs to be.

 

[j_unsuitable]

Posted the same twice cause my PC crashed, sorry