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/

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

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

• How does this work? SUMPRODUCT takes a 2D array, right? (B2:B1400<>"") produces an array of TRUE/FALSE values, but what is the division operator doing, and what about the concatenation? Is that a trick to get COUNTIF to generate an array instead of a single value? – Nick Russo Mar 7 '17 at 21:59
• I tried to explain how it works in another answer here: stackoverflow.com/questions/18586815/… – barry houdini Mar 7 '17 at 23:00

Add a second column with

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

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.

I know this has probably been answered but I was wrestling with this and found the following formula helpful.

=SUM(IF(FREQUENCY(MATCH(\$A\$2:\$A\$14,\$A\$2:\$A\$14,0),MATCH(\$A\$2:\$A\$14,\$A\$2:\$A\$14,0))>0,1))

Basically putting the MATCH formula instead of the cell references. Also found a video on YouTube https://youtu.be/r51RdvOONRQ

You can use the below as an array:

{=SUM(1/COUNTIF(B2:B1400,B2:B1400))}

No need for any helper columns, however, the larger the range the more calculations it will need to iterate through.; but that is true for an array formula.