I'm caculating the number of unique values in a column,
but when the user uses a filter, the number does not
recalculate.

How do I make it recalculate the number of unique values?

Thanks!

you'll need to work with the

=SUBTOTAL() instead of SUM()

see Excel Help for arguments.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jeff" wrote:

I'm caculating the number of unique values in a column,
but when the user uses a filter, the number does not
recalculate.

How do I make it recalculate the number of unique values?

Thanks!

Thanks for the suggestion, but I need to caluculate unique
values, and I don't know how to do that with Subtotal.
Here's my current caculation:

Range("J" & NumRows + 2).Formula =
"=SUM(IF(FREQUENCY(A7:A" & NumRows & ",A7:A" & NumRows & ")
0,1))"


Any other thoughts?

Thanks

-----Original Message-----
you'll need to work with the

=SUBTOTAL() instead of SUM()

see Excel Help for arguments.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jeff" wrote:

I'm caculating the number of unique values in a column,
but when the user uses a filter, the number does not
recalculate.

How do I make it recalculate the number of unique
values?

Thanks!


.

hmm..

I see you problem :(
subtotal wont work in combination with frequency..

afaik you'd need an UDF (vba function) to determine
row visibility as I dont think it can be done with a normal
worksheetfunction or even an old xl5 macro command



Function RowVisible(ref As Range)
Dim r As Long
With ref.EntireRow.Rows
ReDim v(1 To .Count) As Boolean
For r = 1 To .Count
v(r) = Not .Item(r).Hidden
Next
End With
RowVisible = v
End Function

Then

=Sum(n(frequency(a7:a12*n(rowvisible(a7:a12)),a7:a 12)0))


function should be available to the sheet
(which may be a problem...)

N() function converts a boolean to a value

it might work... it's just too complex.... sorry :(


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


wrote:

Thanks for the suggestion, but I need to caluculate unique
values, and I don't know how to do that with Subtotal.
Here's my current caculation:

Range("J" & NumRows + 2).Formula =
"=SUM(IF(FREQUENCY(A7:A" & NumRows & ",A7:A" & NumRows & ")
0,1))"


Any other thoughts?

Thanks

-----Original Message-----
you'll need to work with the

=SUBTOTAL() instead of SUM()

see Excel Help for arguments.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jeff" wrote:

I'm caculating the number of unique values in a column, but when
the
user uses a filter, the number does not recalculate.

How do I make it recalculate the number of unique values?

Thanks!


.