Month Conversion in SUMPRODUCT

[Cheese_whiz]

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

 

[T. Valko]

Try it like this:

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

 

[Bob Phillips]

=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]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[T. Valko]

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(MyOtherWorksheet!K2:K81)

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

 

[Cheese_whiz]

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

 

[Cheese_whiz]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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