• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Urgent formula help

D

DJ

Member
Hi All,


I need an urgent help with a formula.


If we have some numbers Lets say 1,2,2,4,4,4,2,2,1,2 in a range eg. A1:A10. I need count of unique numbers i.e. answer should be 3.


plz help
 
Hi Narayank991,


Thank you so much for the help. However, in a situation if range has text values then it is not working.


Plz help with that too, I will be very grateful.


DJ
 
Hi ,


The following will do the job :


=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))


This is copied from here :


http://www.mrexcel.com/archive/Formulas/11952.html


Narayan
 
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
 
Hi NarayanK991/Hui,


I tried again for same problem and found another workaround which is very easy to understand for everybody.


As I mentioned in my first post "If we have some numbers Lets say 1,2,2,4,4,4,2,2,1,2 in a range eg. A1:A10. I need count of unique numbers". I wrote an array formula =Sum(if(A1:A10=A2:A11,0,1)) in B1 and it's giving correct output for both Numbers and text. The only condition is that data column must be sorted


I just wanted to share it with everybody.


Thank you once again for your help.

DJ
 
You must log in or register to reply here.
Back
Top