Ignore Hidden Rows and Count of distinct values

  • Thread starter Thread starter Anuma (GGK Tech)
  • Start date Start date
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.
 
Look in the help index for SUBTOTAL and pay special attention to hidden rows
part.
 
HI,

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

Please help me to get Unique count.
 
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)))
 
You must log in or register to reply here.

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
Exclude Hidden Row Values 4
Subtotal across a row 1
another COUNTIF question 2
=counta() unless row is hidden 5
CountIf previous cell formula 0
Counting only displayed cells in the column 3
SUMPRODUCT has mismatched range sizes error when sheet opened inGoogle Excel. 1

Back
Top