Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

=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

Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

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

Month Conversion in SUMPRODUCT
 

Thanks to everyone for the patience!

I finally got it working. The problem was that the range included the
header row which was causing the value error. It seems to negate the value
of being able to select list rows if it's going to select the header as well
since most of the time you wouldn't want to treat the header like the rest of
the row, but I digress...

I really do appreciate the help.
CW

"T. Valko" wrote:

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

Month Conversion in SUMPRODUCT
 

I spoke a little too soon. I HAVE resolved the 'value' problem but not the
equation returns true (adds a one to the total) if the 'N' row value is 'yes'
regardless of what the value is in the 'K' row....

Sigh. Progress, though.

Thanks again,
CW

"Cheese_whiz" wrote:

Thanks to everyone for the patience!

I finally got it working. The problem was that the range included the
header row which was causing the value error. It seems to negate the value
of being able to select list rows if it's going to select the header as well
since most of the time you wouldn't want to treat the header like the rest of
the row, but I digress...

I really do appreciate the help.
CW

"T. Valko" wrote:

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

Month Conversion in SUMPRODUCT
 

Copy/paste (do NOT simply retype it) the formula you have in the formula bar
in a response to this message so we can see exactly what you have there.

Rick


"Cheese_whiz" wrote in message
...
I spoke a little too soon. I HAVE resolved the 'value' problem but not the
equation returns true (adds a one to the total) if the 'N' row value is
'yes'
regardless of what the value is in the 'K' row....

Sigh. Progress, though.

Thanks again,
CW

"Cheese_whiz" wrote:

Thanks to everyone for the patience!

I finally got it working. The problem was that the range included the
header row which was causing the value error. It seems to negate the
value
of being able to select list rows if it's going to select the header as
well
since most of the time you wouldn't want to treat the header like the
rest of
the row, but I digress...

I really do appreciate the help.
CW

"T. Valko" wrote:

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

Month Conversion in SUMPRODUCT
 

Hi Rick,

Thanks again. I got it working. I figured I would after you guys helped me
with the value thing. It turns out it was counting cells in the date column
with no date entered as if they satisfied the month condition I was testing
for. I just added another array argument to test to make sure there wasn't a
"" in the date column cell.

There's probably a little more succinct way of handling that situation, but
I'm ok with what I have I think.

Again, thanks for the help. It is much appreciated.
CW

"Rick Rothstein (MVP - VB)" wrote:

Copy/paste (do NOT simply retype it) the formula you have in the formula bar
in a response to this message so we can see exactly what you have there.

Rick


"Cheese_whiz" wrote in message
...
I spoke a little too soon. I HAVE resolved the 'value' problem but not the
equation returns true (adds a one to the total) if the 'N' row value is
'yes'
regardless of what the value is in the 'K' row....

Sigh. Progress, though.

Thanks again,
CW

"Cheese_whiz" wrote:

Thanks to everyone for the patience!

I finally got it working. The problem was that the range included the
header row which was causing the value error. It seems to negate the
value
of being able to select list rows if it's going to select the header as
well
since most of the time you wouldn't want to treat the header like the
rest of
the row, but I digress...

I really do appreciate the help.
CW

"T. Valko" wrote:

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