Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Unique Values Across SpecialCellType Visible Range.
I am trying to sum only the unique values that are visible in a column.
I have an autofilter applied to my worksheet that is hiding dates that are outside of a certain range. In column "Reference No" there are a list of ID values, some of which repeat. If a value repeats, I want to only count the related transaction value, stored in the same row of a different cell, only once. The following code effectively sums unique values across all rows. However, I need it to work only within the range of visible cells. Is this possible using the autofilter, or do I need to add a second argument to my "IF" statement regarding the date range (would prefer not to, since this would inevitably be slower). code: -------------------------------------------------------------------------------- Set rng2 = .Range(.Cells(1, 1), .Cells(2 ^ 16, rng.Count).SpecialCells(xlCellTypeVisible).End(xlU p)) For i = 1 To rng.Count If cName(i) = "Reference No" Then Set rng2 = .Range(.Cells(1, i), .Cells(2 ^ 16, i).End(xlUp)) For j = 2 To rng2.Count istrue = True If .Cells(j, i).Value < .Cells(j + 1, i).Value _ And .Cells(j, tDate).Value LowYear Then totalvalue = totalvalue + .Cells(j, i + 1) istrue = False End If Next j End If Next i -------------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Unique Values Across SpecialCellType Visible Range.
Hi, this is a function I use in one of my add-ins
Function vertSUM(aTable As Range, aCell As Range, Offset As Integer) As Double Dim tmp As Range Dim A As Long Set tmp = aTable.Find(aCell.Text, LookIn:=xlValues, Lookat:=xlWhole) If Not tmp Is Nothing Then A = tmp.Row Do vertSUM = vertSUM + tmp.Offset(, Offset).Value Set tmp = aTable.Find(aCell.Text, tmp, LookIn:=xlValues, Lookat:=xlWhole) If tmp Is Nothing Then Exit Function Loop While tmp.Row < A End If End Function ----------------------- A B 1 a =vertsum($A$7:$B$10;$A1;1) ' 2 3 4 5 6 ID VALUE 'values you can filter 7 a 2 8 b 2 9 a 2 10 b 2 Regards, ste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
count unique values in a filtered range | Excel Worksheet Functions | |||
Unique values from date range | Excel Discussion (Misc queries) | |||
How do I get the unique values from a range? | Excel Worksheet Functions | |||
Display unique values in a range. | Excel Programming |