Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

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

3 Answers 3

up vote 1 down vote accepted

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/

share|improve this answer
    
ok thanks for that –  user1890242 Dec 17 '12 at 11:45
1  
The only way?..... –  barry houdini Dec 17 '12 at 15:05
    
@barryhoudini Thanks, I updated the post... –  Kent Pawar Dec 17 '12 at 15:08

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

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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