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
|