Summing for specific criteria
 

I am working in an Excel Spreadsheet. Column 1 contains
outline levels I need to evaluate, when the number is
greater than 1 I want the activecell to be 3 columns
over. This is the column and cell where I want to sum
all entries where the cells in column 1 are greater than
1. I want the sum to continue until I encounter another
1 in column 1.

I appreciate any help you can give me. So far I have
been able to count the number of rows to sum below each
outline level 1, but am having trouble getting it to do
the sum. I am getting the count with a do loop and then
assigning it to a variable.

Norma

Summing for specific criteria
 

are you summing down or summing up.

--
Regards,
Tom Ogilvy

Norma wrote in message
...
I am working in an Excel Spreadsheet. Column 1 contains
outline levels I need to evaluate, when the number is
greater than 1 I want the activecell to be 3 columns
over. This is the column and cell where I want to sum
all entries where the cells in column 1 are greater than
1. I want the sum to continue until I encounter another
1 in column 1.

I appreciate any help you can give me. So far I have
been able to count the number of rows to sum below each
outline level 1, but am having trouble getting it to do
the sum. I am getting the count with a do loop and then
assigning it to a variable.

Norma

Summing for specific criteria
 

Assumes the sums are put in Column C and the values to be summed are also in
Column C.

Assumes the outline levels are in Column A and there are not blank cells in
column A from row 2 (A2 contains first numeral 1) to the last cell.

Sub AA_Tester1()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Set rng = Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))
Columns(1).Insert
rng.Offset(0, -1).Formula = _
"=if(b2=1,na(),"""")"
Set rng1 = rng.Offset(0, -1).SpecialCells( _
xlFormulas, xlErrors)
rng1.ClearContents
For Each cell In rng1
If Not IsEmpty(cell.Offset(1, 0)) Then
If Not IsEmpty(cell.Offset(2, 0)) Then
Set rng2 = Range(cell.Offset(1, 0), _
cell.Offset(1, 0).End(xlDown))
cell.Offset(0, 3).Formula = "=Sum(" & _
rng2.Offset(0, 3).Address & ")"
Else
cell.Offset(0, 3).Formula = "=" & _
cell.Offset(1, 3).Address
End If
End If
Next
Columns(1).Delete
End Sub

--
Regards,
Tom Ogilvy



NormaD wrote in message
...
I am summing down the column until the outline number
changes back to 1 again.

Thanks,
Norma
-----Original Message-----
are you summing down or summing up.

--
Regards,
Tom Ogilvy

Norma wrote in message
...
I am working in an Excel Spreadsheet. Column 1
contains
outline levels I need to evaluate, when the number is
greater than 1 I want the activecell to be 3 columns
over. This is the column and cell where I want to sum
all entries where the cells in column 1 are greater
than
1. I want the sum to continue until I encounter
another
1 in column 1.

I appreciate any help you can give me. So far I have
been able to count the number of rows to sum below each
outline level 1, but am having trouble getting it to do
the sum. I am getting the count with a do loop and
then
assigning it to a variable.

Norma



.

Summing for specific criteria
 

Tom:

That's so much. That was exactly what I needed. Hope
others out there can get some use out of this code too.

Norma
-----Original Message-----
Assumes the sums are put in Column C and the values to
be summed are also in
Column C.

Assumes the outline levels are in Column A and there are
not blank cells in
column A from row 2 (A2 contains first numeral 1) to the
last cell.

Sub AA_Tester1()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Set rng = Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))
Columns(1).Insert
rng.Offset(0, -1).Formula = _
"=if(b2=1,na(),"""")"
Set rng1 = rng.Offset(0, -1).SpecialCells( _
xlFormulas, xlErrors)
rng1.ClearContents
For Each cell In rng1
If Not IsEmpty(cell.Offset(1, 0)) Then
If Not IsEmpty(cell.Offset(2, 0)) Then
Set rng2 = Range(cell.Offset(1, 0), _
cell.Offset(1, 0).End(xlDown))
cell.Offset(0, 3).Formula = "=Sum(" & _
rng2.Offset(0, 3).Address & ")"
Else
cell.Offset(0, 3).Formula = "=" & _
cell.Offset(1, 3).Address
End If
End If
Next
Columns(1).Delete
End Sub

--
Regards,
Tom Ogilvy



NormaD wrote in message
...
I am summing down the column until the outline number
changes back to 1 again.

Thanks,
Norma
-----Original Message-----
are you summing down or summing up.

--
Regards,
Tom Ogilvy

Norma wrote in message
...
I am working in an Excel Spreadsheet. Column 1
contains
outline levels I need to evaluate, when the number
is
greater than 1 I want the activecell to be 3 columns
over. This is the column and cell where I want to
sum
all entries where the cells in column 1 are greater
than
1. I want the sum to continue until I encounter
another
1 in column 1.

I appreciate any help you can give me. So far I
have
been able to count the number of rows to sum below
each
outline level 1, but am having trouble getting it
to do
the sum. I am getting the count with a do loop and
then
assigning it to a variable.

Norma



.



.