Ignore Hidden Rows and Count of distinct values
 

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.

Ignore Hidden Rows and Count of distinct values
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Anuma (GGK Tech)" wrote in message
...
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.

Ignore Hidden Rows and Count of distinct values
 

HI,

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

Please help me to get Unique count.
--
Anuma Chinthapalli/ http://www.GGKTech.com



"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Anuma (GGK Tech)" wrote in message
...
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.

.

Ignore Hidden Rows and Count of distinct values
 

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Anuma (GGK Tech)" wrote in message
...
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.