Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing on a unique list
I have a spreadsheet that I used the following function to create a unique
list. Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("D2") _ , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom ' Count the number of used rows Set A = Worksheets(1) Set R = A.UsedRange.Cells stRng = R.Rows.Count Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G" & stRng + 4), Unique:=True For each unique value I can have several rows that have amounts in them. What I need to do is create a sum function based on the unique list. Can this be down, and how? TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing on a unique list
Add this
Range("G" & stRng + 4).End(xlDown).Offset(1, 0).FormulaR1C1 = "=SUM(R25C:R[-1]C)" -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Cyberwolf" wrote in message ... I have a spreadsheet that I used the following function to create a unique list. Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("D2") _ , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom ' Count the number of used rows Set A = Worksheets(1) Set R = A.UsedRange.Cells stRng = R.Rows.Count Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G" & stRng + 4), Unique:=True For each unique value I can have several rows that have amounts in them. What I need to do is create a sum function based on the unique list. Can this be down, and how? TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing on a unique list
Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("G" & stRng + 4), Unique:=True Dim rng1 as Range set rng1 = Range("G" & stRng + 4) set rng1 = Range(rng1,rng1.End(xldown)) rng1.offset(0,1).Formula = "=Sumif($I$2:$I$" & stRng & "," & _ rng1.Address(0,0) & ",$J$2:$J$" & stRng & ")" This assumes the column you are summing is column J. Adjust to reflect the column you are summing. -- Regards, Tom Ogilvy "Cyberwolf" wrote: I have a spreadsheet that I used the following function to create a unique list. Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("D2") _ , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom ' Count the number of used rows Set A = Worksheets(1) Set R = A.UsedRange.Cells stRng = R.Rows.Count Range("I2:I" & stRng).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G" & stRng + 4), Unique:=True For each unique value I can have several rows that have amounts in them. What I need to do is create a sum function based on the unique list. Can this be down, and how? TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pick out unique components in a list with unique and common | Excel Discussion (Misc queries) | |||
Summing up Unique Records | Excel Discussion (Misc queries) | |||
Summing Unique Entries | Excel Worksheet Functions | |||
Summing unique values | Excel Worksheet Functions | |||
Summing Unique data values | Excel Worksheet Functions |