Sum Intermitten Data in one Column

[pete.trudell]

Morning, I have a column of data (numbers) which extend down between
rows 4 to 1000, the length varies month to month. The collected data
in sepearated by spaces per staff members. I need to total the
activity for that staff member. See below.

Staff 1
data number Total
data number

Staff 2
data number Total
data number
data number
data number
Staff 3
data number Total
data number
data number
Ect.

As you can see the third column has the values I need to total for each
staff member to place in the first available row in column 4. But since
the number of values are difference from staff member to staff member I
can not figure out how to sum the column?

Thanks

 

[Guest]

REally you need to copy the staff member names down so your table looks more
like this

Staff 1
Staff 1 data number Total
Staff 1 data number

Staff 2
Staff 2 data number Total
Staff 2 data number
Staff 2 data number
Staff 2 data number
Staff 3
Staff 3 data number Total
Staff 3 data number
Staff 3 data number

After that getting the subtotals is not that terribley difficult. Yu could
use subtotaling, pivot tables or even array formulas. Not sure which would be
your favorite so I will let you decide that. To poplulate the first column do
this

Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now
select all of row a which contains the staff members. A4 -> A1000. Hit F5.
Click on the Special Cells Button. Check off Blank cells. All of the blank
cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first
column should now be filled in. Copy and paste special values so that your
formulas become hard coded values and you should be off to the races...

HTH

 

[pete.trudell]

Jim Thanks for the information, but I do not have the luxury of making
changes to the base format..... will be used by State & Feds. So I
can't repeat names. I am designing only the totalling function for
column 7 [Totals]. Here is real data information to give you a better
idea.

Name Type Number Value Units Totals
ABROMS, G.
NM16 3 EMPLOYER RESPONSE 5 15 x
AXEL, R.
CT14 74 RETURN TO WORK 5 370 x
ML06 5 MASS LAYOFF ISSUE 3 15
NM02 1 ABILITY 9 9
NM06 5 AVAILABILITY 8 40
NM16 1 EMPLOYER RESPONSE 5 5
NM22 1 FIRED 43.6 43.6
NM42 2 REMUNERATION 12 24
NM58 3 SUFFICIENT WORK WAGE 5 15
ARMSTRONG, R.
NM02 2 ABILITY 9 18 X
NM06 4 AVAILABILITY 8 32
NM10 4 BASE PERIOD WAGES 12 48
NM16 14 EMPLOYER RESPONSE 5 70
NM22 108 FIRED 43.6 4708.8
NM40 59 QUIT 40 2360
NM42 2 REMUNERATION 12 24
NM46 1 SUITABLE WORK 15 15
NM56 18 VALID CLAIM 13 234
BROWN, B
NM02 4 ABILITY 9 36 X
NM06 9 AVAILABILITY 8 72
NM42 1 REMUNERATION 12 12
NM56 5 VALID CLAIM 13 65
NX04 1 INELIGIBILITY REVIEW 15 15

As you can see the number and type of cases varies, so I need to devise
a programming or formula which totals the units column values for each
staff member.

Thanks Though. Pete

 

[Guest]

Based on that you are probably looking for a custom programming kind of a
function. Let me take a look at it and see what I can do for you. This will
not be something that you will easily be able to modify on your own.

Jim Thanks for the information, but I do not have the luxury of making
changes to the base format..... will be used by State & Feds. So I
can't repeat names. I am designing only the totalling function for
column 7 [Totals]. Here is real data information to give you a better
idea.

Name Type Number Value Units Totals
ABROMS, G.
NM16 3 EMPLOYER RESPONSE 5 15 x
AXEL, R.
CT14 74 RETURN TO WORK 5 370 x
ML06 5 MASS LAYOFF ISSUE 3 15
NM02 1 ABILITY 9 9
NM06 5 AVAILABILITY 8 40
NM16 1 EMPLOYER RESPONSE 5 5
NM22 1 FIRED 43.6 43.6
NM42 2 REMUNERATION 12 24
NM58 3 SUFFICIENT WORK WAGE 5 15
ARMSTRONG, R.
NM02 2 ABILITY 9 18 X
NM06 4 AVAILABILITY 8 32
NM10 4 BASE PERIOD WAGES 12 48
NM16 14 EMPLOYER RESPONSE 5 70
NM22 108 FIRED 43.6 4708.8
NM40 59 QUIT 40 2360
NM42 2 REMUNERATION 12 24
NM46 1 SUITABLE WORK 15 15
NM56 18 VALID CLAIM 13 234
BROWN, B
NM02 4 ABILITY 9 36 X
NM06 9 AVAILABILITY 8 72
NM42 1 REMUNERATION 12 12
NM56 5 VALID CLAIM 13 65
NX04 1 INELIGIBILITY REVIEW 15 15

As you can see the number and type of cases varies, so I need to devise
a programming or formula which totals the units column values for each
staff member.

Thanks Though. Pete

REally you need to copy the staff member names down so your table looks more
like this

Staff 1
Staff 1 data number Total
Staff 1 data number

Staff 2
Staff 2 data number Total
Staff 2 data number
Staff 2 data number
Staff 2 data number
Staff 3
Staff 3 data number Total
Staff 3 data number
Staff 3 data number

After that getting the subtotals is not that terribley difficult. Yu could
use subtotaling, pivot tables or even array formulas. Not sure which would be
your favorite so I will let you decide that. To poplulate the first column do
this

Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now
select all of row a which contains the staff members. A4 -> A1000. Hit F5.
Click on the Special Cells Button. Check off Blank cells. All of the blank
cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first
column should now be filled in. Copy and paste special values so that your
formulas become hard coded values and you should be off to the races...

HTH

 

[Guest]

Are you wanting to sum the number, the value the units or the units times the
value?

Jim Thanks for the information, but I do not have the luxury of making
changes to the base format..... will be used by State & Feds. So I
can't repeat names. I am designing only the totalling function for
column 7 [Totals]. Here is real data information to give you a better
idea.

Name Type Number Value Units Totals
ABROMS, G.
NM16 3 EMPLOYER RESPONSE 5 15 x
AXEL, R.
CT14 74 RETURN TO WORK 5 370 x
ML06 5 MASS LAYOFF ISSUE 3 15
NM02 1 ABILITY 9 9
NM06 5 AVAILABILITY 8 40
NM16 1 EMPLOYER RESPONSE 5 5
NM22 1 FIRED 43.6 43.6
NM42 2 REMUNERATION 12 24
NM58 3 SUFFICIENT WORK WAGE 5 15
ARMSTRONG, R.
NM02 2 ABILITY 9 18 X
NM06 4 AVAILABILITY 8 32
NM10 4 BASE PERIOD WAGES 12 48
NM16 14 EMPLOYER RESPONSE 5 70
NM22 108 FIRED 43.6 4708.8
NM40 59 QUIT 40 2360
NM42 2 REMUNERATION 12 24
NM46 1 SUITABLE WORK 15 15
NM56 18 VALID CLAIM 13 234
BROWN, B
NM02 4 ABILITY 9 36 X
NM06 9 AVAILABILITY 8 72
NM42 1 REMUNERATION 12 12
NM56 5 VALID CLAIM 13 65
NX04 1 INELIGIBILITY REVIEW 15 15

As you can see the number and type of cases varies, so I need to devise
a programming or formula which totals the units column values for each
staff member.

Thanks Though. Pete

REally you need to copy the staff member names down so your table looks more
like this

Staff 1
Staff 1 data number Total
Staff 1 data number

Staff 2
Staff 2 data number Total
Staff 2 data number
Staff 2 data number
Staff 2 data number
Staff 3
Staff 3 data number Total
Staff 3 data number
Staff 3 data number

After that getting the subtotals is not that terribley difficult. Yu could
use subtotaling, pivot tables or even array formulas. Not sure which would be
your favorite so I will let you decide that. To poplulate the first column do
this

Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now
select all of row a which contains the staff members. A4 -> A1000. Hit F5.
Click on the Special Cells Button. Check off Blank cells. All of the blank
cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first
column should now be filled in. Copy and paste special values so that your
formulas become hard coded values and you should be off to the races...

HTH

 

[Guest]

Here is some code. It assumes you want to sum up the 6th column. You can
change this by modifying the const at the top of the code. It also assumes
that your first name is in Cell A2. You can modify that also... change Set
rngName = wks.Range("A2") to whatever cell makes you the happiest..


Option Explicit

Const m_cSumColumn As Integer = 5

Public Sub AddSubtotals()
Dim wks As Worksheet
Dim rngName As Range
Dim rngToSum As Range

Set wks = ActiveSheet
Set rngName = wks.Range("A2")
Set rngToSum = Range(rngName, rngName.End(xlDown)).Offset(0, m_cSumColumn)

Do While rngName.Row <> 65536
rngName.Offset(1, 6).Value = Application.Sum(rngToSum)
Set rngName = rngName.End(xlDown)
Set rngToSum = Range(rngName, rngName.End(xlDown)).Offset(0,
m_cSumColumn)
Loop

End Sub

HTH

Jim Thanks for the information, but I do not have the luxury of making
changes to the base format..... will be used by State & Feds. So I
can't repeat names. I am designing only the totalling function for
column 7 [Totals]. Here is real data information to give you a better
idea.

Name Type Number Value Units Totals
ABROMS, G.
NM16 3 EMPLOYER RESPONSE 5 15 x
AXEL, R.
CT14 74 RETURN TO WORK 5 370 x
ML06 5 MASS LAYOFF ISSUE 3 15
NM02 1 ABILITY 9 9
NM06 5 AVAILABILITY 8 40
NM16 1 EMPLOYER RESPONSE 5 5
NM22 1 FIRED 43.6 43.6
NM42 2 REMUNERATION 12 24
NM58 3 SUFFICIENT WORK WAGE 5 15
ARMSTRONG, R.
NM02 2 ABILITY 9 18 X
NM06 4 AVAILABILITY 8 32
NM10 4 BASE PERIOD WAGES 12 48
NM16 14 EMPLOYER RESPONSE 5 70
NM22 108 FIRED 43.6 4708.8
NM40 59 QUIT 40 2360
NM42 2 REMUNERATION 12 24
NM46 1 SUITABLE WORK 15 15
NM56 18 VALID CLAIM 13 234
BROWN, B
NM02 4 ABILITY 9 36 X
NM06 9 AVAILABILITY 8 72
NM42 1 REMUNERATION 12 12
NM56 5 VALID CLAIM 13 65
NX04 1 INELIGIBILITY REVIEW 15 15

As you can see the number and type of cases varies, so I need to devise
a programming or formula which totals the units column values for each
staff member.

Thanks Though. Pete

REally you need to copy the staff member names down so your table looks more
like this

Staff 1
Staff 1 data number Total
Staff 1 data number

Staff 2
Staff 2 data number Total
Staff 2 data number
Staff 2 data number
Staff 2 data number
Staff 3
Staff 3 data number Total
Staff 3 data number
Staff 3 data number

After that getting the subtotals is not that terribley difficult. Yu could
use subtotaling, pivot tables or even array formulas. Not sure which would be
your favorite so I will let you decide that. To poplulate the first column do
this

Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now
select all of row a which contains the staff members. A4 -> A1000. Hit F5.
Click on the Special Cells Button. Check off Blank cells. All of the blank
cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first
column should now be filled in. Copy and paste special values so that your
formulas become hard coded values and you should be off to the races...

HTH