Count of unique filtered values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am still trying to get a count of unique values in a filtered column.

The formula which someone posted does not work. (I get a #NAME error) from the following formula:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10))-(COUNTA(A6:A10)<>SUBTOTAL(3,A6:A10))

I tried writing a function in VB as follows:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim ct As Long
ct = 0
For Each c In rng.Cells
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) Then
Set fc = rng.Find(what:=c.Value, after:=c)
If (fc Is Nothing) Then ct = ct + 1
End If
Next
CtUnqFiltered = ct
End Function

This function should work, only counting the last of a unique value in the list, using the 'Find' The problem is that the 'Find' always returns nothing(never finds the value), even if there is another of the same value later in the range. Am i doing something wrong with the 'find'? The doc says it should return a the cell where it finds the value.

TIA,
DOTJake
 
You have to download and install Laurent Longre's Morefunc

Here's an English description

http://www.rhdatasolutions.com/morefunc/

Can be downloaded here

http://longre.free.fr/english/

--

Regards,

Peo Sjoblom


DOTJake said:
I am still trying to get a count of unique values in a filtered column.

The formula which someone posted does not work. (I get a #NAME error) from the following formula:
Click to expand...
=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A10)-MIN(ROW(A6:A10)),,1)),A6:A10)
)-(COUNTA(A6:A10) said:
I tried writing a function in VB as follows:

Function CtUnqFiltered(rng As Range) As Long
Dim fc As Excel.Range
Dim ct As Long
ct = 0
For Each c In rng.Cells
If (Not rng.Rows(c.Row - rng.Rows.Count).Hidden) And (Not IsEmpty(c)) Then
Set fc = rng.Find(what:=c.Value, after:=c)
If (fc Is Nothing) Then ct = ct + 1
End If
Next
CtUnqFiltered = ct
End Function

This function should work, only counting the last of a unique value in the
Click to expand...
list, using the 'Find' The problem is that the 'Find' always returns
nothing(never finds the value), even if there is another of the same value
later in the range. Am i doing something wrong with the 'find'? The doc
says it should return a the cell where it finds the value.
 
Back
Top