# Sub-totaling and Totaling question

#### nikneven

##### Board Regular
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
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....

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Analyzed Site Payroll 9-13.xls
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

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.

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

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

I have about 300 sets of these...
Analyzed Site Payroll 9-13.xls
ABCDEFGHIJKLMNOPQRSTU
1DateCallTypeCallTimeNameBookedStraightBookedOTWorkedStraightWorkedOTNotesPMEventDayRateOTRateStraightPayOTPayTotal
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

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

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``````

In a spare column concatenate the first and last name, eg:

=E2&F2

Then subtotal at each change in that column.

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

Replies
7
Views
205
Replies
5
Views
424
Replies
3
Views
232
Replies
2
Views
97
Replies
4
Views
154

1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back