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.

 

[yogeshmaney]

=If(And(A2<=1/5,A2>=5/31),3,"")

 

[yogeshmaney]

={count(if(A2:A100>=1/5,if(A2:A100<=5/31,B2:B100)))}

 

[Randolf]

Thank you for the quick reply.
But I would need to count and entire column of data.
Column A has over 20000 dates ad colum B has thousands of values. I would need a foumula that would count columns B when in the data range of column A.

 

[Domenic]

Assuming that A2:B6 contains the data, let D2 contain the start date, and E2 contain the end date. Then try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

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

 

[sulakvea]

try this... entered as array (with ctrl+shift+enter). again, assuming that your data sits in columns A1:B100, here it is:

=COUNT(IF((A1:A100-StartDate)*(A1:A100-EndData)<0,B1:B100,FALSE))

P.S. this will not count the start/end dates, so anything strictly in between. if you want Start/End included, change it to this (again, entered as array)

=COUNT(IF((A1:A100-StartDate+0.00001)*(A1:A100-EndDate-0.00001)<0,B1:B100,FALSE))

StartDate and End Date are the respective cell references

 

[Randolf]

Thanks Dom,

Please correct me if I am wrong. But if seems link the formula that you provided would be like a concatenation and them looking for unique values. This gives me a much greater number that I exepct.
My appoligies but the Date range also includes a time stamp, which seems to be affecting the formula.

for example
<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0 x:str><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 4640" width=145><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 109pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=145 height=17 x:num="40178.561076388891">12/31/2009 13:27</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=72 x:num>1838348</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.568761574075">12/31/2009 13:39</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1732004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.604328703703">12/31/2009 14:30</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1826638</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.608668981484">12/31/2009 14:36</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838367</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.608807870369">12/31/2009 14:36</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838742</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.608935185184">12/31/2009 14:36</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838378</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.705289351848">12/31/2009 16:55</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838348</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.705810185187">12/31/2009 16:56</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838378</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40178.986122685186">12/31/2009 23:40</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1838378</TD></TR></TBODY></TABLE>
This would give me the result of 9 when I need to be 6

 

[yogeshmaney]

If u try this

={COUNT(IF(A1:A9>=Start Date,IF(A1:A9>=End Date,B1:B9)))}

 

[Domenic]

Thanks Dom,

Please correct me if I am wrong. But if seems link the formula that you provided would be like a concatenation and them looking for unique values. This gives me a much greater number that I exepct.
My appoligies but the Date range also includes a time stamp, which seems to be affecting the formula.

for example
<table style="width: 163pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="217"><colgroup><col style="width: 109pt;" width="145"><col style="width: 54pt;" width="72"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); width: 109pt; height: 12.75pt; background-color: transparent;" x:num="40178.561076388891" align="right" width="145" height="17">12/31/2009 13:27</td><td style="border: medium none rgb(236, 233, 216); width: 54pt; background-color: transparent;" x:num="" align="right" width="72">1838348</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.568761574075" align="right" height="17">12/31/2009 13:39</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1732004</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.604328703703" align="right" height="17">12/31/2009 14:30</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1826638</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.608668981484" align="right" height="17">12/31/2009 14:36</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838367</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.608807870369" align="right" height="17">12/31/2009 14:36</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838742</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.608935185184" align="right" height="17">12/31/2009 14:36</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838378</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.705289351848" align="right" height="17">12/31/2009 16:55</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838348</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.705810185187" align="right" height="17">12/31/2009 16:56</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838378</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="40178.986122685186" align="right" height="17">12/31/2009 23:40</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:num="" align="right">1838378</td></tr></tbody></table>
This would give me the result of 9 when I need to be 6

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.

 

[LearnMeExcel]

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.

Hi
Dom why u use # in ur formulla

&"#"&

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

&"|"&