count occurences meeting criteria
 

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

count occurences meeting criteria
 

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

count occurences meeting criteria
 

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

count occurences meeting criteria
 

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

count occurences meeting criteria
 

What's wrong with using the formula that Rick gave you?

Unless you have XL2007, you cannot use an entire row as a range for
Sumproduct. You must use a specific range. So if your paste somehow "gets
rid of the A1 and IV1", then put it back. If your range ends at DD1, then
use:

=Sumproduct(--(Year(a1:dd1)=Year(Now())))

Regards.
Fred

"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

count occurences meeting criteria
 

Unless you have XL2007, you cannot use an entire row as a range for
Sumproduct. You must use a specific range. So if your paste somehow "gets
rid of the A1 and IV1", then put it back. If your range ends at DD1, then
use:

Apparently you can... for rows. This works fine in my XL2003...

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

where I modified the formula to look at rows 1 and 2. And Excel did change
the A1:IV1 range to 1:1 automatically.

Rick

count occurences meeting criteria
 

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

count occurences meeting criteria
 

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

count occurences meeting criteria
 

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?

Yes, that makes a difference and is probably why you're getting the error.

YEAR("emp") = #VALUE!

The YEAR function is expecting a date value.


--
Biff
Microsoft Excel MVP


"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