I must be going senile, as I can usually crack these things, but I am having
massive problems counting dates.

In column A I have the date entered as follows:
=TEXT(WORKDAY(A636,1),"dd-mmm-yy")

I need to count every date where the year is equal to the current year.

Please put me out of my misery and I apologise for such a dumb ass question.

Richard

Try

=SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY())))

Mike

"Richard" wrote:

> I must be going senile, as I can usually crack these things, but I am having
> massive problems counting dates.
>
> In column A I have the date entered as follows:
> =TEXT(WORKDAY(A636,1),"dd-mmm-yy")
>
> I need to count every date where the year is equal to the current year.
>
> Please put me out of my misery and I apologise for such a dumb ass question.
>
> Richard

Mike

Thanks for the reply. I found the issue was that I had entered
=sumproduct(--(year(a:a)=year(today()))) with there being a lable (column
header) in cell a2. I kept getting a return of #NUM!

Is there a way around the label issue.

Richard

"Mike H" wrote:

> Try
>
> =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY())))
>
> Mike
>
> "Richard" wrote:
>
> > I must be going senile, as I can usually crack these things, but I am having
> > massive problems counting dates.
> >
> > In column A I have the date entered as follows:
> > =TEXT(WORKDAY(A636,1),"dd-mmm-yy")
> >
> > I need to count every date where the year is equal to the current year.
> >
> > Please put me out of my misery and I apologise for such a dumb ass question.
> >
> > Richard

Hi,

=SUMPRODUCT(--(YEAR(A2:A65536)=YEAR(TODAY())))


Mike

"Richard" wrote:

> Mike
>
> Thanks for the reply. I found the issue was that I had entered
> =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column
> header) in cell a2. I kept getting a return of #NUM!
>
> Is there a way around the label issue.
>
> Richard
>
> "Mike H" wrote:
>
> > Try
> >
> > =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY())))
> >
> > Mike
> >
> > "Richard" wrote:
> >
> > > I must be going senile, as I can usually crack these things, but I am having
> > > massive problems counting dates.
> > >
> > > In column A I have the date entered as follows:
> > > =TEXT(WORKDAY(A636,1),"dd-mmm-yy")
> > >
> > > I need to count every date where the year is equal to the current year.
> > >
> > > Please put me out of my misery and I apologise for such a dumb ass question.
> > >
> > > Richard

I forgot to mention Sumproduct will not accept A:A as a range

"Richard" wrote:

> Mike
>
> Thanks for the reply. I found the issue was that I had entered
> =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column
> header) in cell a2. I kept getting a return of #NUM!
>
> Is there a way around the label issue.
>
> Richard
>
> "Mike H" wrote:
>
> > Try
> >
> > =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY())))
> >
> > Mike
> >
> > "Richard" wrote:
> >
> > > I must be going senile, as I can usually crack these things, but I am having
> > > massive problems counting dates.
> > >
> > > In column A I have the date entered as follows:
> > > =TEXT(WORKDAY(A636,1),"dd-mmm-yy")
> > >
> > > I need to count every date where the year is equal to the current year.
> > >
> > > Please put me out of my misery and I apologise for such a dumb ass question.
> > >
> > > Richard

On Thu, 19 Jun 2008 04:15:02 -0700, Richard <(E-Mail Removed)>
wrote:

>Mike
>
>Thanks for the reply. I found the issue was that I had entered
>=sumproduct(--(year(a:a)=year(today()))) with there being a lable (column
>header) in cell a2. I kept getting a return of #NUM!
>
>Is there a way around the label issue.
>
>Richard

I'm surprised you are getting a #NUM! error and not a #VALUE! error.

Since you have specified an entire column as your argument, I am assuming you
are using Excel 2007. That being the case, you can try this **array** formula:

=SUM(--(IF(ISNUMBER(--A:A),YEAR(A:A))=YEAR(TODAY())))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.

If you are not using Excel 2007, then your arguments must not encompass an
entire column. e.g:

=SUM(--(IF(ISNUMBER(--A1:A65535),YEAR(A1:A65535))=YEAR(TODAY())))

--ron

No, I am not using 2007, I just tried a:a sorry it is lack of sleep.

at least I understand now

thanks for the help

"Ron Rosenfeld" wrote:

> On Thu, 19 Jun 2008 04:15:02 -0700, Richard <(E-Mail Removed)>
> wrote:
>
> >Mike
> >
> >Thanks for the reply. I found the issue was that I had entered
> >=sumproduct(--(year(a:a)=year(today()))) with there being a lable (column
> >header) in cell a2. I kept getting a return of #NUM!
> >
> >Is there a way around the label issue.
> >
> >Richard
>
> I'm surprised you are getting a #NUM! error and not a #VALUE! error.
>
> Since you have specified an entire column as your argument, I am assuming you
> are using Excel 2007. That being the case, you can try this **array** formula:
>
> =SUM(--(IF(ISNUMBER(--A:A),YEAR(A:A))=YEAR(TODAY())))
>
> To enter an array formula, hold down <ctrl><shift> while hitting <enter>. Excel
> will place braces {...} around the formula.
>
> If you are not using Excel 2007, then your arguments must not encompass an
> entire column. e.g:
>
> =SUM(--(IF(ISNUMBER(--A1:A65535),YEAR(A1:A65535))=YEAR(TODAY())))
>
> --ron
>