Count Unique Values but not Filtered or Hidden Values

[Lee]

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

 

[Shane Devenshire]

First problem is that AutoFilters don't work for rows only columns.

If in fact you are really filtering in column then you need something of the
form

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C1:C19,ROW(1:19)-1,,1))*(C1:C19="Red"))

 

[T. Valko]

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.

 

[T. Valko]

Ooops! Typo:

......OFFSET(I2,.....

Should be:

......OFFSET(I1,.....