Count Unique Values but not Filtered or Hidden Values
 

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

Count Unique Values but not Filtered or Hidden Values
 

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"))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Lee" wrote:

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

Count Unique Values but not Filtered or Hidden Values
 

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

Count Unique Values but not Filtered or Hidden Values
 

Ooops! Typo:

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

Should be:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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