Date Count

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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

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
Click to expand...

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
 
Back
Top