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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count unique values with auto-filter on 6
Count Unique Values in a entire column, not just range 20
count unique values in a filtered range 2
Need array formula to ignore hidden values 1
use a function for filtered data 6
need help on either doing a loop and counting specific values orusing subtotal function 2
Sum after Filter, Count after Fliter 2
Counting unique values by date 10

Back
Top