To count the unique numeric values (or dates) in a filtered range...

Assume no empty cells within the range:

Array entered**

=COUNT(1/FREQUENCY(IF(SUBTOTAL(2,OFFSET(I2,ROW(I1:I255)-ROW(I1),)),MATCH(I1:I255,I1:I255,0)),ROW(I1:I255)-ROW(I1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
Hello,
I'm trying to count unique values in a row and have a filter at the same
time. I'm using the formula =SUM(IF(FREQUENCY(I1:I255,I1:I255)0,1))
When
I use a filter it doesn't recalculate. I tried to use the subtotal 109
function but i'm appearantly not inserting it correctly.
Can anyone help?
Thanks,
Lee