Sub-totaling and Totaling question

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
This is part of a sheet that i have that totals employee hours and pay per week. It looks like this....
Analyzed Site Payroll 9-13.xls
YZAAABACADAE
1Day RateOT RateStraight PayOT PayTotal
2$ 200.00$ 300.00$ 200.00$ 30.00
3$ 200.00$ 300.00$ 100.00$ 150.00
4$ 200.00$ 300.00$ 60.00$ 60.00
5
6
7$ 220.00$ 330.00$ 220.00$ -
8$ 220.00$ 330.00$ -$ -
9$ 220.00$ 330.00$ 330.00$ -
10$ 220.00$ 330.00$ 110.00$ -
11$ 220.00$ 330.00$ 176.00$ -
12$ 220.00$ 330.00$ 110.00$ -
13$ 220.00$ 330.00$ 110.00$ -
14$ 220.00$ 330.00$ 110.00$ -
15$ 220.00$ 330.00$ 220.00$ -
16
17
18$ 500.00$ 750.00$ -$ -
19$ 500.00$ 750.00$ 850.00$ 225.00
20$ 500.00$ 750.00$ 900.00$ -
21$ 500.00$ 750.00$1,000.00$ -
22$ 500.00$ 750.00$ 450.00$ 75.00
23$ 500.00$ 750.00$ 250.00$ 375.00
24
WEEK OF 9-13


I'd like it to look like this without having to subtotal and total every one by hand....
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
Analyzed Site Payroll 9-13.xls
YZAAABACAD
1DayRateOTRateStraightPayOTPayTotal
2$200.00$300.00$200.00$30.00
3$200.00$300.00$100.00$150.00
4$200.00$300.00$60.00$60.00
5$360.00$240.00$600.00
6
7$220.00$330.00$220.00$-
8$220.00$330.00$-$-
9$220.00$330.00$330.00$-
10$220.00$330.00$110.00$-
11$220.00$330.00$176.00$-
12$220.00$330.00$110.00$-
13$220.00$330.00$110.00$-
14$220.00$330.00$110.00$-
15$220.00$330.00$220.00$-
16$1,386.00$-$1,386.00
17
18$500.00$750.00$-$-
19$500.00$750.00$850.00$225.00
20$500.00$750.00$900.00$-
21$500.00$750.00$1,000.00$-
22$500.00$750.00$450.00$75.00
23$500.00$750.00$250.00$375.00
24$3,450.00$675.00$4,125.00
25
WEEK OF 9-13


any ideas? totaling all 300 employees is a real pain...

thanks
nik
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Select your data and choose Data|Subtotals from the menu. At each change in Day Rate, Use function Sum, Add subtotal to Straight pay and OT pay and click OK.

You may want to remove the blank rows first, fill in the Total using SUM and include that column in Add subtotal to.
 

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
I've tried that, unfortunatly there are people next to each other that have the same rates, so it doesn't properly subtotal them. I've tried to do it by name, but i cant seem to make it total the correct rows.

Nik
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

I don't see any people in what you posted. You seem to be just subtotalling when the Day rate changes.

For more help you will need to post more representative data.
 

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
I have about 300 sets of these...
Analyzed Site Payroll 9-13.xls
ABCDEFGHIJKLMNOPQRSTU
1DateCallTypeCallTimeNameBookedStraightBookedOTWorkedStraightWorkedOTNotesPMEventDayRateOTRateStraightPayOTPayTotal
209/10/06LoadIn2:00PM2FirstName{Confirmed}100101PM:(XXX)XXX-XXXXShowName$200.00$300.00$200.00$30.00
309/12/06Strike1:00AM3FirstName{Confirmed}5555PM:(XXX)XXX-XXXXShowName$200.00$300.00$100.00$150.00
409/12/06Strike9:00PM6FirstName{Confirmed}3232PM:(XXX)XXX-XXXXShowName$200.00$300.00$60.00$60.00
5
WEEK OF 9-13
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

What determines where one group starts and the next group ends? If it's just 2 blank rows, how did they get there?
 

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
Well, obviously the names differ, though we have people with the same first names, and some with the same last names. the two blank rows are there to create room for me to total each person and for our comptroller to easily read the sheet for entering it into payroll. the rows get there with this macro.

Code:
Sub DTS9()
Dim i As Long
For i = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, 6) <> "" And Cells(i, 6) <> Cells(i + 1, 6) Or Cells(i, 5) <> Cells(i + 1, 5) Then
Cells(i + 1, 6).EntireRow.Insert Shift:=xlDown
Cells(i + 1, 6).EntireRow.Insert Shift:=xlDown
End If
Next
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In a spare column concatenate the first and last name, eg:

=E2&F2

Then subtotal at each change in that column.
 

nikneven

Board Regular
Joined
Mar 20, 2006
Messages
117
didn't work. i still couldn't get that to work. its not totaling them. ****. by hand it is.... the grand total thing is messing everything up. and i cant get them to add across into the total column
 

Watch MrExcel Video

Forum statistics

Threads
1,113,990
Messages
5,545,370
Members
410,679
Latest member
rolandbianco
Top