Function to subtotal by employee and also by month for each employ

[jeremiah]

There are 50 employees and have a variable number of records for each
employee. I can easily subtotal by each employee however I need also to
subtotal each month for each employee. All works well until I hit one of
those employees whose 1st month total is the same as the previous employees
last month total. I have figured out how to group each employees records
together but cannot figure out how to subtotal the month within each group.
Any help is appreciated. As below, when I hit one of the records in question
my monthly totals now show up below the grand totals, then throwing the
remainder of the spreadsheet off also.

5/23/2009 5 Jane Doe1 282160
5/30/2009 5 Jane Doe1 259456
5 Total 541616
6/6/2009 6 Jane Doe1 251424
6 Total 251424
Jane Doe 1 Total 793040
5/16/2009 5 Jon Doe 14636
5/23/2009 5 Jon Doe 103154
Jon Doe Total 117790
5 Total 117790
5/16/2009 5 Jane Doe 2 104204
Jane Doe 2 Total 104204
5 Total 104204

 

[Joel]

Try this. I've done this type of problem before. I used inserted worksheet
formulas into the worksheet to perform the totals.

Sub GetTotals()

'sort data
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:" & Lastrow).Sort _
header:=xlNo, _
key1:=Range("C1"), _
order1:=xlAscending, _
key2:=Range("A1"), _
order2:=xlAscending

RowCount = 1
StartEmployee = RowCount
StartMonth = RowCount

Do While Range("A" & RowCount) <> ""
'check if month or emplyee doesn't match
If Month(Range("A" & RowCount)) <> _
Month(Range("A" & (RowCount + 1))) Or _
Range("C" & RowCount) <> Range("C" & (RowCount + 1)) Then

Rows(RowCount + 1).Insert
Range("A" & (RowCount + 1)) = "Monthly Total"
Range("B" & (RowCount + 1)).Formula = _
"=Sum(B" & StartMonth & ":B" & RowCount & ")"
Range("D" & (RowCount + 1)).Formula = _
"=Sum(D" & StartMonth & "" & RowCount & ")"

'check if employee doesn't match
If Range("C" & RowCount) <> _
Range("C" & (RowCount + 2)) Then

Rows(RowCount + 2).Insert
Range("A" & (RowCount + 2)) = "Employee Total"
Range("B" & (RowCount + 2)).Formula = _
"=Sumproduct(" & _
"--(A" & StartEmployee & ":A" & RowCount & "<>""Monthly
Total"")," & _
"B" & StartEmployee & ":B" & RowCount & ")"
Range("D" & (RowCount + 2)).Formula = _
"=Sumproduct(" & _
"--(A" & StartEmployee & ":A" & RowCount & "<>""Monthly
Total"")," & _
"D" & StartEmployee & "" & RowCount & ")"

RowCount = RowCount + 3
StartMonth = RowCount
StartEmployee = RowCount
Else
RowCount = RowCount + 2
StartMonth = RowCount
End If
Else
RowCount = RowCount + 1
End If

Loop


End Sub

 

[Bernd P]

Hello,

Please have a look at my UDF Sfreq:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd

 

[jeremiah]

Thanks so much, I do have Joel's solution working but will figure out which
one works best for my needs. You guys are amazing!