You can't put the formula in a cell that includes that cell as part of the
formula's range. Either put the formula on a different row or, if you are
going to put the formula in IV1, the change the range to A1:IU1 or, as I
said in one of my other posts, to a smaller range (less cells in the range
means the formula is more efficient).

Rick


"geebee" (noSPAMs) wrote in message
...
hi,

i tried:
=SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW())))

in cell IV1, and i am getting an #value! error message.

does it matter that some of the columns heading in row 1 are like "emp",
"building name" and so forth, in addition to the date value columns?





"Rick Rothstein (MVP - VB)" wrote:

Yes, if you specify the whole row, Excel will change A1:IV1 to 1:1 (I put
it
in the way I did so you could see how to change it if you were going to
specify a smaller range than the entire row... the less cells being
processed by SUMPRODUCT, the more efficient the calculation is).

Did you try my formula **as I posted it**? If not, try it and then let us
know the results (don't modify it as you showed you did in your last
message; copy/paste it exactly as I wrote it).

Rick


"geebee" (noSPAMs) wrote in message
...
here is what i have now in cell IV1:

=SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2)))

now i am getting a #value error. i noticed that when i paste your
formula
in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there.



"Rick Rothstein (MVP - VB)" wrote:

Are your "dates" (the Jan-07 for example) text or real Excel dates? If
they
are real dates, you could use this...

=SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW())))

If you have a maximum column that your data goes to, use that column
designation in place of the IV column designation that I used.

Rick


"geebee" (noSPAMs) wrote in message
...
hi,

i have a row 1, and values across the columns in row 1 like "Jan-07"
...
dates randing from "Jan-06" through the present month. I am trying
to
put
a
formula somewhere in the worksheet that counts the number of cells
in
the
forst row that have a year equal to the current year. Here is what
I
have,
as an array formula:

=COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2)

but it is returning FALSE

what am i doing wrong? if possible i would like to use a countif or
something else besides an array formula.

thanks in advance,
geebee