Distinct Count in Excel Pivot Table
 

Hi,

How to use DISTINCT COUNT in Excel pivot table data? The
summarization options provided by Execl pivot do not
contain this option.

Thanks fo your support,

Shanmugavel

Distinct Count in Excel Pivot Table
 

I found this on another posting at

http://www.mrexcel.com/archive/Formulas/14269.html

Distinct values within a column
Posted by Andrew Miller on January 09, 2002 1:43 PM
Is there any way to calculate how many distinct values appear in
column. My column is much larger with many more values, but fo
instance say I have a column that has 3 cells in it. The value in th
first cell is DO12, the second is DO12, and the third is DO13. I onl
have two distinct values (DO12 and DO13) in this column. Is there an
way to automatically return a value that tells me the number o
distinct values? Thanks.

Distinct values within a column
Posted by Mark W. on January 09, 2002 2:09 PM
Is your list sorted? If so, use...

{=COUNTA(A1:A3)-SUM((A1:A3=OFFSET(A1:A3,1,))+0)}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.


Or more simply...
Posted by Mark W. on January 09, 2002 2:15 PM
{=SUM((A1:A3<OFFSET(A1:A3,1,))+0)}

-----------------------------------------------

And it works. For more information on Array formulae, see the Exce
Help

spike

--
spike
-----------------------------------------------------------------------
spikey's Profile: http://www.excelforum.com/member.php...fo&userid=1550
View this thread: http://www.excelforum.com/showthread.php?threadid=18160

Distinct Count in Excel Pivot Table
 

This will only work if the data is sorted.

You would get better solutions if you posted this over in
Worksheet.Functions.

--
Regards,
Tom Ogilvy

"spikey" wrote in message
...

I found this on another posting at

http://www.mrexcel.com/archive/Formulas/14269.html

Distinct values within a column
Posted by Andrew Miller on January 09, 2002 1:43 PM
Is there any way to calculate how many distinct values appear in a
column. My column is much larger with many more values, but for
instance say I have a column that has 3 cells in it. The value in the
first cell is DO12, the second is DO12, and the third is DO13. I only
have two distinct values (DO12 and DO13) in this column. Is there any
way to automatically return a value that tells me the number of
distinct values? Thanks.

Distinct values within a column
Posted by Mark W. on January 09, 2002 2:09 PM
Is your list sorted? If so, use...

{=COUNTA(A1:A3)-SUM((A1:A3=OFFSET(A1:A3,1,))+0)}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.


Or more simply...
Posted by Mark W. on January 09, 2002 2:15 PM
{=SUM((A1:A3<OFFSET(A1:A3,1,))+0)}

-----------------------------------------------

And it works. For more information on Array formulae, see the Excel
Help

spikey


--
spikey
------------------------------------------------------------------------
spikey's Profile:
http://www.excelforum.com/member.php...o&userid=15505
View this thread: http://www.excelforum.com/showthread...hreadid=181609