Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
distinct count summarize Data for pivoit table | Excel Discussion (Misc queries) | |||
How do I set up Distinct Count in a Pivot Table? | Excel Discussion (Misc queries) | |||
count distinct in Pivot table | Excel Discussion (Misc queries) | |||
Count Distinct Values by Group Using Pivot Table (NM) | Excel Worksheet Functions | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) |