8

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?

user1890242
  • 81

6 Answers6

10

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

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

barry houdini
  • 11,212
2

Modern answer that makes it really simple (requires Excel 365, Excel 2021, or newer):

=COUNTA(UNIQUE(B2:B1400))

wisbucky
  • 3,346
2

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/

Community
  • 1
Kent Pawar
  • 682
1

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
  • 11
1

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.

0

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
  • 7,595