Ignore Hidden Rows and Count of distinct values

A

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.
 
D

Don Guillett

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

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.
 
A

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

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 Distinct Values based on criteria 4
another COUNTIF question 2
Exclude Hidden Row Values 4
=counta() unless row is hidden 5
Count non-hidden rows 5
Is it possible to ONLY count values that appear within UNHIDDEN ce 6
Countif excluding hidden rows 4
How do I ignore values while copying from hidden rows? 1

Top