7

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
  • 71
  • 1
  • 1
  • 3
  • 1
    You can always do "remove duplicates" this will tell you how many items were removed –  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

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
  • 10,892
  • 1
  • 20
  • 25
  • 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 07 '17 at 21:59
  • 1
    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 Mar 07 '17 at 23:00
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
  • 652
  • 1
  • 9
  • 25
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
1

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

=COUNTA(UNIQUE(B2:B1400))

wisbucky
  • 2,928
  • 31
  • 29
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,377
  • 20
  • 54
  • 82