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?

microsoft-excel

user1890242

Posted 2012-12-17T10:09:19.163

Reputation: 61

1You can always do "remove duplicates" this will tell you how many items were removed – None – 2012-12-17T10:11:12.820

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 – 2012-12-17T16:30:51.837

Answers

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/

Kent Pawar

Posted 2012-12-17T10:09:19.163

Reputation: 562

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

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

barry houdini

Posted 2012-12-17T10:09:19.163

Reputation: 10 434

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 – 2017-03-07T21:59:37.210

I tried to explain how it works in another answer here: http://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel/18588144#18588144

– barry houdini – 2017-03-07T23:00:25.193

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

Simon Cubbin

Posted 2012-12-17T10:09:19.163

Reputation: 11

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.

allaphor

Posted 2012-12-17T10:09:19.163

Reputation:

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.

PeterH

Posted 2012-12-17T10:09:19.163

Reputation: 5 346