Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing specific Columns based on Criteria | Excel Discussion (Misc queries) | |||
Summing specific cells on two worksheets | Excel Discussion (Misc queries) | |||
Summing specific cells on two worksheets | Excel Worksheet Functions | |||
Summing specific cells! | Excel Worksheet Functions | |||
Summing specific texts | Excel Discussion (Misc queries) |