Re: Month Conversion in SUMPRODUCT

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

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


--
Biff
Microsoft Excel MVP


"Cheese_whiz" <Cheesewhiz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:870FE7E6-D82E-46FE-A276-8661AD5A9992@xxxxxxxxxxxxxxxx
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" <Cheesewhiz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D964E21E-7342-432E-921C-442A8DA71C32@xxxxxxxxxxxxxxxx
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" <Cheesewhiz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E6FC437-D720-4C0B-9CBA-CAF87054D1AA@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Month Conversion in SUMPRODUCT
    ... Copy/paste the formula you have in the formula bar in a response to this message so we can see exactly what you have there. ... header row which was causing the value error. ... > If every cell in the range has a date and if they're true Excel dates> then ... The cell ranges were correct. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: linked cells change to literals
    ... Notice that excel changed the format of B1 to match the format in A1. ... I just select the cell, and reformat it to General. ... 'detail-data'!a1 should be the header 110-04128 SBC in the summary. ...
    (microsoft.public.excel.misc)
  • Re: Month Conversion in SUMPRODUCT
    ... If every cell in the range has a date and if they're true Excel dates then ... those dashes in the original post were just mistakes in the ... The cell ranges were correct. ... maybe it was something like a reference missing ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Month Conversion in SUMPRODUCT
    ... header row which was causing the value error. ... If every cell in the range has a date and if they're true Excel dates then ... The cell ranges were correct. ... maybe it was something like a reference missing ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Duplicate value with blank space
    ... suggestion for the following: - ... ¡§ Invoice Date¡¨ in cell B1 as Column B header with the following ... I need a dynamic excel ...
    (microsoft.public.excel.programming)