# Count unique names in excel column range

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

``````=SUM(IF(FREQUENCY(B2:B1400,B2:B1400)>0,1))
``````

Would someone know how?

-

## migrated from stackoverflow.comDec 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

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.. http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

-
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

`=SUMPRODUCT((B2:B1400<>"")/COUNTIF(B2:B1400,B2:B1400&""))`

-

``````=1/COUNTIF(\$B\$2:\$B\$1400;B2)