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
--------------------------------------------------------------------------------

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