Ignore Hidden Rows and Count of distinct values

[Anuma (GGK Tech)]

Hi,

I need to display the Count of distinct values and also ignore hidden rows
in the count.
Please help me to fix this issue.

 

[Don Guillett]

Look in the help index for SUBTOTAL and pay special attention to hidden rows
part.

 

[Anuma (GGK Tech)]

HI,

I have tried "=SUBTOTAL(103,A3:A1000 )" function. But i am not getting
unique values count.

Please help me to get Unique count.

 

[Ashish Mathur]

Hi,

Try this. Assumed that data is in L3:21

=COUNT(1/FREQUENCY(IF(SUBTOTAL(103,OFFSET(L3,ROW(L3:L21)-ROW(L3),)),MATCH(L3:L21,L3:L21,0)),ROW(L3:L21)-ROW(L3)))