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
|