Prev: formating
Next: Duplicates formula
From: Anuma (GGK Tech) on 12 Apr 2010 10:39
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.
From: Don Guillett on 12 Apr 2010 12:15
Look in the help index for SUBTOTAL and pay special attention to hidden rows
part.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Anuma (GGK Tech)" <AnumaGGKTech(a)discussions.microsoft.com> wrote in message
news:8A8B8ACB-7912-4554-90A7-5EE4987A1616(a)microsoft.com...
> 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.

From: Anuma (GGK Tech) on 12 Apr 2010 13:11
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
> dguillett(a)gmail.com
> "Anuma (GGK Tech)" <AnumaGGKTech(a)discussions.microsoft.com> wrote in message
> news:8A8B8ACB-7912-4554-90A7-5EE4987A1616(a)microsoft.com...
> > 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.
>
> .
>
From: Ashish Mathur on 12 Apr 2010 21:00
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)" <AnumaGGKTech(a)discussions.microsoft.com> wrote in message
news:8A8B8ACB-7912-4554-90A7-5EE4987A1616(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: formating
Next: Duplicates formula