Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
function problem regarding cell range | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |