Sum Data in a Dynamic Range

txaggie96

New Member
Joined
Dec 22, 2008
Messages
3
I have a spreadsheet with employee names, jobs, and their work hours per month, as shown below. I pull this data from a large database and I use VBA code get it into this form.

Name Job Apr May Jun
Bill Job1 10 10 10
Bill
Joe Job1 20 10 20
Joe Job2 10 10 0
Joe Job3 0 5 5
Joe
John Job1 20 10 10
John Job2 10 0 0
John
and so on...

Employees can have one or more jobs and it changes monthly. I would like to be able to sum the hours per month and put the totals on the blank row under the last job listed. Does that make sense? I have stared at this for awhile and just can't figure out a way to do it. I cannot use formulas in the cells. I would need to use VBA code as the sheet gets cleared every month so new data can go on it. Any help would be appreciated. Thanks.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Why not have a data dump as you have done in one sheet and create a dynamic named range, or table for this data. In another sheet create a pivot table that references this data and every time you update your data sheet just refresh your pivot table to update?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try this:-
The Total Hours for each Persons jobs will be shown in the Blank column "B" cell against their name.
The code when rerun, will overwrite any Numerical data in that cell from a previous code run.
Code:
Sub tots()
Dim Last As Long, RngAc As Range, Tot As Integer, SumTot As Long
Dim Temp As String
Last = Range("A" & Rows.Count).End(xlUp).Row
 For Tot = 1 To Last
            Temp = Cells(Tot, "A")
        Do While Cells(Tot, "A") = Temp
            Set RngAc = Range(Range("C" & Tot), Cells(Tot, Columns.Count).End(xlToLeft))
                    If IsNumeric(Cells(Tot, "B")) Or Cells(Tot, "B") = "" Then Cells(Tot, "B") = SumTot
                        SumTot = SumTot + Application.Sum(RngAc)
                            Tot = Tot + 1
        Loop
     Tot = Tot - 1
    SumTot = 0
Next Tot
End Sub
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top