Counting Unique Numbers in Excel

Kyle Witter
Kyle Witter used Ask the Experts™
on
I'm using Excel 2010 to count unique numbers in a column, but I can't get it to output properly.  Does anyone have idea what I'm doing wrong? unique-sample.xlsx

All I want to see is the number of different ID numbers in my column of numbers.  I'd prefer to set the formula for the entire column if need be, Starting at Row 5 where the data will be in the finished spreadsheet.  All data above Row 5 will contain merged cells that should not be included in the results.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
THis array formula will do it:

{=SUM(1/COUNTIF(A2:A376,A2:A376))}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.
Top Expert 2010
Commented:
To ignore the stuff above Row 5:

{=SUM(1/COUNTIF(A5:A376,A5:A376))}
Kent DyerIT Security Analyst Senior

Commented:
While there maybe other ways to do this..  Probably the easiest way to do this is to use a SQL statement:

SELECT DISTINCT COLUMN_NAME FROM TABLE ORDER BY COLUMN_NAME

Open in new window


HTH,

Kent
Top Expert 2010

Commented:
Kent,

Leaving aside for the moment that this is an Excel question, not a database question, I think the Asker is looking for a distinct count.  Thus:

SELECT COUNT(DISTINCT COLUMN_NAME) AS UniqueNumbers FROM TABLE

Open in new window


:)

Patrick
AnilData Manager

Commented:
In Named Range "List"  = =Sheet1!$A$5:$A$380

I moved the list down 5 cells.


Attached file.

A>


unique-sample.xlsx
AnilData Manager

Commented:
IF patrick is right, then my answer only produces a distinct list. The count of that column will give you the answer. There are formulae for getting it more directly.
Please give an example of what you want to help you.

A>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial