MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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.

Posted by Mark W. on January 09, 2002 2:15 PM

Or more simply...

{=SUM((A1:A3<>OFFSET(A1:A3,1,))+0)}

Posted by Aladin Akyurek on January 09, 2002 3:10 PM

Also... .

ERR