Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
       200    Pass
ABC    201    Pass    
       202    fail    

       300    Pass
DEF    201    Pass
       202    Fail

I have a table which looks like the one shown above. The cells on the left are merged (i.e. ABC is one merged cell and DEF is another). I want to count the number of unique entries in the second column.

=SUM(IF(FREQUENCY(IF(LEN(C4:C4700)>0,MATCH(C4:C4700,C4:C4700,0),""), 
        IF(LEN(C4:C4700)>0,MATCH(C4:C4700,C4:C4700,0),""))>0,
        1))

When the above formula is used to count the number of unique entries, there is an error. When calculation steps were followed, I found that Excel was not able to calculate LEN(C4:C4700).

Can somebody help me with this?

share|improve this question

migrated from stackoverflow.com Aug 22 '11 at 13:41

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

3  
Please pay attention to formatting. Your question was barely legible. I've corrected it for you this time. – Jean-François Corbett Aug 22 '11 at 13:27

this should help you

=SUMPRODUCT((B1:B4700<>"")/COUNTIF(B1:B4700,B1:B4700&""))

share|improve this answer
1  
How does it work? – Tom Wijsman Aug 22 '11 at 15:36

Your Answer

 
discard

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