5

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.com Dec 17 '12 at 15:00

This question came from our site for professional and enthusiast programmers.

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

9

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
  • 1
    I tried to explain how it works in another answer here: stackoverflow.com/questions/18586815/… – barry houdini Mar 7 '17 at 23:00
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.

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.