Excel Formula to track different pay rates

Microsoft Office excel 2007 home & stude...
July 21, 2010 at 09:47:16
Specs: Windows 7, Pentium 4 1.8 GHZ /
I work in a bar/grill, and I am trying to set up a spreadsheet to help me keep track of all my hours and tips. I hit a setback while trying to set my totals column due to the fact that we have two separate pay rates, one for bar and one for kitchen.

The way I have my spreadsheet set up now, I have 5 columns: Date, Shift, Pay Rate, Hours, Tips. I want to set up a small chart showing all of my total bar hours, total kitchen hours, total combined hours, and total tips earned.

My problem is setting the formulas for my totals chart to distinguish between bar hours and kitchen hours. My first thought was to use the SUMIF formula, but I cannot get it to work out right. Any ideas/suggestions?

See More: Excel Formula to track different pay rates

Report •

July 21, 2010 at 10:14:05

What data in your worksheet distinguishes between Bar and Kitchen shifts.

If you have a column with "B" or "K" in it, say column G
and Pay is in column C
using the 16 shifts in rows 2 to 17
Enter "B" in cell H18
and this in cell I19
and you get the total Bar pay for the selected period
Enter "K" in H19
Drag the formula down a row and you get the Kitchen pay in the selected period.

Without more concrete information, that's about as far as I can go.


Report •

July 21, 2010 at 10:59:01
Sorry for the lack of detail.

I have it set up like this:
A3:A9 is each day of the week
B3:B9 is each shift I have for that day
C3:C9 is the pay rate. I entered "$7.25" for kitchen hours and "$2.13" for bar hours.
D3:D9 is the hours I worked for that individual shift.
E3:E9 is the tips I collected for that individual shift.

I have a small chart underneath for totals.
B12 is bar hours for that week.
B13 is kitchen hours for that week.
B14 is total combined hours for that week.
B15 is total tips for that week.

We are on a 2 week pay period, so I have the exact same charts copied starting at column G. I also have a small chart starting at column M with totals for the 2 week pay period.

Here is an example of exactly what I want to do:
For Tuesday, 7/20 (A3) I had a Close Bar shift (B3). The pay rate was $2.13 (C3) and the hours worked were 5:53 (D3).

I can't figure out how to set up the Weekly Totals formula to add up my total hours for the week so if the Pay Rate is $2.13 those hours will be added under Bar Hours, and if the Pay Rate is $7.25 those hours will be added under Kitchen Hours.

I think this is a little more clear than my original post, I'm not sure of the best way to describe exactly what I am looking to do. Please let me know if there is any more information I can give to help.

Report •

July 23, 2010 at 04:46:24

To add up bar or kitchen hours you can use the SUMIF() function. It will add numbers in one column, if cells in the corresponding row in a different column match a criteria.

I could have used this:
This would have given the total kitchen hours in the first week's hours column (cells D3 to D9)
The problem with this is that the formula will fail if the pay rate changes.

What I have done is use columns F and K to flag whether the hours were Kitchen or Bar.
I used this formula in cell F3:
This works on the basis that there are two pay rates and if the rate in cell C3 is as large as the largest in column C, it must be the Kitchen rate, and displays "K".
If it's not the highest rate, it displays "B".
If the hours in column D are zero/empty cell it displays a blank cell.
If you use 0 for no hours or leave the hours cell empty, the OR function should return TRUE for 0 or "".

This isn't perfect - it would fail if all shifts were Bar shifts that week
Also if the pay rate changed mid-week it would fail.

I am sure that there could be variations on this formula to make it more robust, but I offer it as a starter
Drag the formula in cell F3, down to F9
Copy D3 to F9 and paste into K3 to K9

Now the hours:
In cell B12 enter this:
Format the cell as [h]:mm
The formula will sum all the hours where there is a "B" in column F
The format allows hours over 24 to display as hours as opposed to being converted into days.

In cell B13 do the same, but the formula looks for "K" in column F

In cell B14 I entered this formula:
Again format as [h]:mm
I used this longer formula as a way of testing that the two COUNTIF formulas had captured all the hours. If not you get "Error" displayed.

In cell B15 enter
and format as a number with two decimal places.

Copy B12 to B15 and paste into G12 to G15

Hope this helps.


Report •

Related Solutions

Ask Question