Function - Counting Years in range
 

Hi,

I've got a list of around 300 dates Format e.g 01/07/2006 for different
years. Also some entries have ' in front of them to make them text

I want to count the number of dates in say A2 to A300 (may have blanks)
and then count the how many in current year 2006.

Would like it to return something like "There are 200 entries in
current year out of 299".

Thanks in advance

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562126

Function - Counting Years in range
 

VBA Noob wrote:
Hi,

I've got a list of around 300 dates Format e.g 01/07/2006 for different
years. Also some entries have ' in front of them to make them text

I want to count the number of dates in say A2 to A300 (may have blanks)
and then count the how many in current year 2006.

Would like it to return something like "There are 200 entries in
current year out of 299".

Thanks in advance

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562126

Hi,

Maybe you can use something like:

="There are
"&SUMPRODUCT(--(RIGHT(TEXT(A2:A300,"dd/mm/yyyy"),4)="2006"))&" entries
in current year out of "&COUNTA(A2:A300)

Regards,
Bondi

Function - Counting Years in range
 

Thanks

Worked a treat.

I got the right function and count function part but it was how to sum
them that stump me was trying Countif

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562126

Function - Counting Years in range
 

Another alternative...
=SUMPRODUCT(--(TEXT(A2:A300,"yyyy")="2006"))
or
=SUMPRODUCT(--(year(A2:A300)=2006))



Bondi wrote:

VBA Noob wrote:
Hi,

I've got a list of around 300 dates Format e.g 01/07/2006 for different
years. Also some entries have ' in front of them to make them text

I want to count the number of dates in say A2 to A300 (may have blanks)
and then count the how many in current year 2006.

Would like it to return something like "There are 200 entries in
current year out of 299".

Thanks in advance

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562126

Hi,

Maybe you can use something like:

="There are
"&SUMPRODUCT(--(RIGHT(TEXT(A2:A300,"dd/mm/yyyy"),4)="2006"))&" entries
in current year out of "&COUNTA(A2:A300)

Regards,
Bondi

--

Dave Peterson

Function - Counting Years in range
 

Thanks Dave


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562126