Hi all,

I'm trying to count the number of rows in a worksheet list that have two
conditions. One of the rows is formated as a date and I need to convert that
to a month number prior to the '=' sign in that condition. I tried this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

Try it like this:

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!K2:K81)=2))


--
Biff
Microsoft Excel MVP


"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have two
conditions. One of the rows is formated as a date and I need to convert
that
to a month number prior to the '=' sign in that condition. I tried this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!k2:k81)=2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have two
conditions. One of the rows is formated as a date and I need to convert
that
to a month number prior to the '=' sign in that condition. I tried this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

Thanks for the replies,

I still get the 'value?' error. It indicates that a value used in the code
is not of the right data type. I've checked the column where the dates are
and it is formatted as a date with the option chosen that looks like this:
3/14/01

Thanks again,
CW

"Bob Phillips" wrote:

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!k2:k81)=2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have two
conditions. One of the rows is formated as a date and I need to convert
that
to a month number prior to the '=' sign in that condition. I tried this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

Did you use exactly what Bob and Biff posted, namely ranges with colons
separating the cell references, or did you simply modify your originally
posted code leaving in the dashes that you showed originally?

Rick


"Cheese_whiz" wrote in message
...
Thanks for the replies,

I still get the 'value?' error. It indicates that a value used in the
code
is not of the right data type. I've checked the column where the dates
are
and it is formatted as a date with the option chosen that looks like this:
3/14/01

Thanks again,
CW

"Bob Phillips" wrote:

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!k2:k81)=2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have
two
conditions. One of the rows is formated as a date and I need to
convert
that
to a month number prior to the '=' sign in that condition. I tried
this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to
convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

Hi Rick,

To be honest, those dashes in the original post were just mistakes in the
post, not the actual code I used. Not sure why I did that.

I copy/pasted the code provided from the above. I changed the worksheet
name to match my own. The cell ranges were correct. I had already tried
that code earlier because I found a post somewhere that suggested it, but I
was at work and it didn't work for me. I decided to try it again because I
was at home thinking if it DID work and I got to work and tried again and it
still wouldn't work there, maybe it was something like a reference missing or
something.

Thanks,
CW



"Rick Rothstein (MVP - VB)" wrote:

Did you use exactly what Bob and Biff posted, namely ranges with colons
separating the cell references, or did you simply modify your originally
posted code leaving in the dashes that you showed originally?

Rick


"Cheese_whiz" wrote in message
...
Thanks for the replies,

I still get the 'value?' error. It indicates that a value used in the
code
is not of the right data type. I've checked the column where the dates
are
and it is formatted as a date with the option chosen that looks like this:
3/14/01

Thanks again,
CW

"Bob Phillips" wrote:

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!k2:k81)=2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have
two
conditions. One of the rows is formated as a date and I need to
convert
that
to a month number prior to the '=' sign in that condition. I tried
this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to
convert
the date column into month numbers.

Any suggestions?

Thanks,
CW

The only thing that'll cause a #VALUE! error is if your dates aren't true
Excel dates.

Month(MyOtherWorksheet!K2:K81)

If every cell in the range has a date and if they're true Excel dates then
this formula should return TRUE:

=COUNT(MyOtherWorksheet!K2:K81)=ROWS(MyOtherWorksh eet!K2:K81)

Might there be formulas in the range that return formula blanks? They will
cause the error.


--
Biff
Microsoft Excel MVP


"Cheese_whiz" wrote in message
...
Hi Rick,

To be honest, those dashes in the original post were just mistakes in the
post, not the actual code I used. Not sure why I did that.

I copy/pasted the code provided from the above. I changed the worksheet
name to match my own. The cell ranges were correct. I had already tried
that code earlier because I found a post somewhere that suggested it, but
I
was at work and it didn't work for me. I decided to try it again because
I
was at home thinking if it DID work and I got to work and tried again and
it
still wouldn't work there, maybe it was something like a reference missing
or
something.

Thanks,
CW



"Rick Rothstein (MVP - VB)" wrote:

Did you use exactly what Bob and Biff posted, namely ranges with colons
separating the cell references, or did you simply modify your originally
posted code leaving in the dashes that you showed originally?

Rick


"Cheese_whiz" wrote in message
...
Thanks for the replies,

I still get the 'value?' error. It indicates that a value used in the
code
is not of the right data type. I've checked the column where the dates
are
and it is formatted as a date with the option chosen that looks like
this:
3/14/01

Thanks again,
CW

"Bob Phillips" wrote:

=SUMPRODUCT(--('MyOtherWorksheet'!N2:N81="Yes"),--(Month('MyOtherWorksheet!k2:k81)=2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Cheese_whiz" wrote in message
...
Hi all,

I'm trying to count the number of rows in a worksheet list that have
two
conditions. One of the rows is formated as a date and I need to
convert
that
to a month number prior to the '=' sign in that condition. I tried
this:

=SUMPRODUCT('MyOtherWorksheet'!N2-N81="Yes")*(Month('MyOtherWorksheet!k2-k81)=2))

I'm getting a 'value' error which must be from the part trying to
convert
the date column into month numbers.

Any suggestions?

Thanks,
CW