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.
       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

1 Answer 1

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.