Count Unique Values but not Filtered or Hidden Values

  • Thread starter Thread starter Lee
  • Start date Start date
L

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
 
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"))
 
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.
 
Back
Top