Finding the unique distinct values based on a condition

[kirangarapati]

Hello All


I am trying to find the unique distinct value in Column B based on the Value in Column A


Year Situation

1/3/2011 AK3289

2/5/2011

4/1/2011 AM9832

5/3/2011 AK3289

9/4/2011 PMY0983

28/8/2011

1/5/2012 AF2345

1/22/2012 AK2134

3/4/2012

4/5/2012

3/9/2012 AF2345

10/5/2012 AK2134

11/12/2012

8/19/2012 AL8765

7/23/2012


To be specific, I would like to count the unique distinct number of situations in 2011 and 2012.


I have used the below formula to get the number of situations for 2011 and 2012. However, these do not give me the unique distinct number

for 2011: =COUNTIFS(A:A, ">="&DATE(2011,1,1),A:A, "<="&DATE(2011,12,31),B:B, "*")

for 2012: =COUNTIFS(A:A, ">="&DATE(2012,1,1),A:A, "<="&DATE(2012,12,31),B:B, "*")


Answer: The answer should be

2011 = 3

2012 = 3


Thank You

 

[Debraj]

Hi Kiran,


I wish you have Excel >= 2007..


Plese try the below Formula, and don't forget to confirm the Formula by pressing Ctrl + Shift + Enter Not just Enter.


Please 1st try, if it works, then you can increase the Size as required.


=SUM(IF(($A$1:$A$500>DATE(2011,1,1)) * ($A$1:$A$500<DATE(2011,12,31) * (B1:B500<>"")), 1 / COUNTIFS($A$1:$A$500,">" & DATE(2011,1,1),$A$1:$A$500,"<" & DATE(2011,12,31),$B$1:$B$500, "<>" & "",$B$1:$B$500,$B$1:$B$500)))


Regards,

Deb..


PS: If possible, ignore to increase the size as musch as possible.

 

[NARAYANK991]

Hi ,


You can go through this link for a detailed explanation :


http://www.get-digital-help.com/2009/11/14/count-unique-distinct-values-using-date-criteria-in-a-range-in-excel/


The formula to be used is :


=SUM(--(FREQUENCY(IF(YEAR(A2:A16)=2011,IF(B2:B16<>"",COUNTIF(B2:B16,"<"&B2:B16)+1,""),""),COUNTIF(B2:B16,"<"&B2:B16)+1)>0))


entered as an array formula , using CTRL SHIFT ENTER.


Narayan

 

[kirangarapati]

Hello Deb


I have 2010. Your formula worked like a charm.


However, I do not understand what is being done in the formula. Could you please explain.


Also, how would the formula change if i do not want to mention 500 cells. What if I want excel to read the column A and Column B rather than mentioning A1, A500, B1 and B500.


Narayan: I tried the formula you mentioned. However, it does not work. Gives me 0. I entered it as an array formula.


Thank You

 

[NARAYANK991]

Hi ,


I copied and pasted the formula from my post , and it shows me the correct value. If it does not work for you , you should look at what changes need to be made to suit the data in your worksheet , such as cell addresses.


Narayan

 

[kirangarapati]

Hello Narayan


You are right. I made a mistake. Your formula works too.


However, I have to mention the range of cells manually. What if I would like excel to go through the entire column and give me the result.


Thank You

 

[mnair77]

You could try making your data into a table and using structured references for the ranges in your formula.

 

[NARAYANK991]

Hi ,


You could use named ranges ; create a named range , say :


Date_Range , and in the Refers To box , put in the following formula :


=Sheet_name!$A$2:INDEX(Sheet_name!$A:$A,COUNTA(Sheet_name!$A:$A))


Situation , and in the Refers To box , put in the following formula :


=Sheet_name!$B$2:INDEX(Sheet_name!$B:$B,COUNTA(Sheet_name!$A:$A))


The formula would now become :


=SUM(--(FREQUENCY(IF(YEAR(Date_Range)=2011,IF(Situation<>"",COUNTIF(Situation,"<"&Situation)+1,""),""),COUNTIF(Situation,"<"&Situation)+1)>0))


Narayan