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
|