Formula to count unique Values in a date range needed.

[Randolf]

Hi,

I am trying to count the number of unique vaules in column B that fall with in a date range in column A.

For example
A: B:
12/31/2009 1111111
1/5/2010 1111111
1/5/2010 1111111
5/31/2010 2222222
5/31/2010 1111111

If the date range is 1/5 to 5/31 the result would be 3.

I apperciate any help.
I have tried countif and if statement. I assume that this may be an array but I have not used them before.

 

[Aladin Akyurek]

Hi
Dom why u use # in ur formulla

&"#"&

and in some cases i see u and aladin use another way

&"|"&

We don't want to confound records like

A1: 1, B1: 11
A2: 11, B2: 1

by copying down

=A1&B1

A separator like "|" or "#" avoids having

111

instead of 1|11 and 11|1.

 

[LearnMeExcel]

Thanx Aladin
that is mean we just used this kind of CONCATENATION when we need to use two or more columns as one criteria

 

[Aladin Akyurek]

Thanx Aladin
that is mean we just used this kind of CONCATENATION when we need to use two or more columns as one criteria

Yes, because concatenation without a separator can lead to errors in processing.

Mind you, the dinstinction is a default assumption here.

 

[LearnMeExcel]

Mind you, the dinstinction is a default assumption here.

Hi Aladin
Sorry i didn't get the idea

 

[Aladin Akyurek]

Hi Aladin
Sorry i didn't get the idea

The default assumption is that we want to have the parts/items recognizable (distinct) after we glue them together. Hence a separator. It's imaginable that there can be situations in which we don't want the distinction.

 

[LearnMeExcel]

thanx Aladin

 

[Domenic]

In that case, try the following instead...

=SUM(IF(FREQUENCY(IF($A$2:$A$10>=D2,IF($A$2:$A$10<=E2,MATCH(INT($A$2:$A$10)&"#"&INT($B$2:$B$10),INT($A$2:$A$10)&"#"&INT($B$2:$B$10),0))),ROW($A$2:$A$10)-ROW($A$2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Correction, no need to subject Column B to INT...

=SUM(IF(FREQUENCY(IF($A$2:$A$10>=D2,IF($A$2:$A$10<=E2,MATCH(INT($A$2:$A$10)&"#"&$B$2:$B$10,INT($A$2:$A$10)&"#"&
$B$2:$B$10,0))),ROW($A$2:$A$10)-ROW($A$2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.