Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an excel spread sheet that contains a list of server names in column B. The server names appear multiple times in this column.

I would like to count the number of unique server names in a give range.

The range is B2:B1400

I tried this but it returned 0


Would someone know how?

share|improve this question

migrated from Dec 17 '12 at 15:00

This question came from our site for professional and enthusiast programmers.

You can always do "remove duplicates" this will tell you how many items were removed – Constanta Dec 17 '12 at 10:11
The formula you suggested is valid but only for counting distinct numbers in the range - for text or numbers (or a combination of both) you can use the formula I suggest below – barry houdini Dec 17 '12 at 16:30
up vote 1 down vote accepted

FREQUENCY doesn't work like that.

One of the quickest ways to get the distinct count would be to first filter out the duplicates and then select the records, right-click on bottom bar of Excel and check 'Count'.

See this post for getting the distinct values in Excel.

UPDATE: There are a bunch of ways you could achieve this..

share|improve this answer
ok thanks for that – user1890242 Dec 17 '12 at 11:45
The only way?..... – barry houdini Dec 17 '12 at 15:05
@barryhoudini Thanks, I updated the post... – Kent Pawar Dec 17 '12 at 15:08

This formula will give you a "distinct count" without any helper columns


share|improve this answer

Add a second column with


and then sum the column.

Each row will have a value corresponding to it's fraction of the total number of similar occurrences. The sum of all rows will give you the distinct count.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .