Evaluate sumproduct question

When I use the code below I receive the #value! error message. Can someone
explain what i'm doing wrong?

Range("b27") = Evaluate("=sumproduct((active!A1:A24
=01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")


Thanks for your time and help

Try

Range("B27").Value =
Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
"(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ram" <[email protected]> wrote in message
news:[email protected]...
> When I use the code below I receive the #value! error message. Can someone
> explain what i'm doing wrong?
>
> Range("b27") = Evaluate("=sumproduct((active!A1:A24
> =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
>
>
> Thanks for your time and help

Hi Bob,

When I used your code i recevied the #REF! error. Any suggestions?


Thanks again


"Bob Phillips" wrote:

> Try
>
> Range("B27").Value =
> Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ram" <[email protected]> wrote in message
> news:[email protected]...
> > When I use the code below I receive the #value! error message. Can someone
> > explain what i'm doing wrong?
> >
> > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> >
> >
> > Thanks for your time and help
>
>
>

What do you get if you put the equivalent formula in B27 manually?

If that returns a #ref! error, do you have any #ref! errors in your data
(a1:a24, c1:c24, or d1:d24 of the Active sheet?



ram wrote:
>
> Hi Bob,
>
> When I used your code i recevied the #REF! error. Any suggestions?
>
> Thanks again
>
> "Bob Phillips" wrote:
>
> > Try
> >
> > Range("B27").Value =
> > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "ram" <[email protected]> wrote in message
> > news:[email protected]...
> > > When I use the code below I receive the #value! error message. Can someone
> > > explain what i'm doing wrong?
> > >
> > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > >
> > >
> > > Thanks for your time and help
> >
> >
> >

--

Dave Peterson

That sounds that you don't have a worksheet called ACTIVE

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ram" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> When I used your code i recevied the #REF! error. Any suggestions?
>
>
> Thanks again
>
>
> "Bob Phillips" wrote:
>
> > Try
> >
> > Range("B27").Value =
> > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "ram" <[email protected]> wrote in message
> > news:[email protected]...
> > > When I use the code below I receive the #value! error message. Can
someone
> > > explain what i'm doing wrong?
> > >
> > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > >
> > >
> > > Thanks for your time and help
> >
> >
> >

I had to use date value and it works great

Range("B27").Value =
Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""01/01/2005""))*(Active!C1:C24=614546),Active!K1:K24)")

Thanks for all the help guys

"Bob Phillips" wrote:

> That sounds that you don't have a worksheet called ACTIVE
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ram" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > When I used your code i recevied the #REF! error. Any suggestions?
> >
> >
> > Thanks again
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Try
> > >
> > > Range("B27").Value =
> > > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "ram" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > When I use the code below I receive the #value! error message. Can
> someone
> > > > explain what i'm doing wrong?
> > > >
> > > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > > >
> > > >
> > > > Thanks for your time and help
> > >
> > >
> > >
>
>
>

I like to use:

date(2005,1,1)
(It's unambiguous)

Then I don't have to worry about datevalue() returning an incorrect date.

does
=datevalue("01/02/2005")
refer to Jan 2nd, 2005 or Feb 1st, 2005?

ram wrote:
>
> I had to use date value and it works great
>
> Range("B27").Value =
> Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""01/01/2005""))*(Active!C1:C24=614546),Active!K1:K24)")
>
> Thanks for all the help guys
>
> "Bob Phillips" wrote:
>
> > That sounds that you don't have a worksheet called ACTIVE
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "ram" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > When I used your code i recevied the #REF! error. Any suggestions?
> > >
> > >
> > > Thanks again
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Try
> > > >
> > > > Range("B27").Value =
> > > > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > > > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "ram" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > When I use the code below I receive the #value! error message. Can
> > someone
> > > > > explain what i'm doing wrong?
> > > > >
> > > > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > > > >
> > > > >
> > > > > Thanks for your time and help
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson

You make a great point

Thanks

"Dave Peterson" wrote:

> I like to use:
>
> date(2005,1,1)
> (It's unambiguous)
>
> Then I don't have to worry about datevalue() returning an incorrect date.
>
> does
> =datevalue("01/02/2005")
> refer to Jan 2nd, 2005 or Feb 1st, 2005?
>
> ram wrote:
> >
> > I had to use date value and it works great
> >
> > Range("B27").Value =
> > Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""01/01/2005""))*(Active!C1:C24=614546),Active!K1:K24)")
> >
> > Thanks for all the help guys
> >
> > "Bob Phillips" wrote:
> >
> > > That sounds that you don't have a worksheet called ACTIVE
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "ram" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > When I used your code i recevied the #REF! error. Any suggestions?
> > > >
> > > >
> > > > Thanks again
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Try
> > > > >
> > > > > Range("B27").Value =
> > > > > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > > > > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "ram" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > When I use the code below I receive the #value! error message. Can
> > > someone
> > > > > > explain what i'm doing wrong?
> > > > > >
> > > > > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > > > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > > > > >
> > > > > >
> > > > > > Thanks for your time and help
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

That is why I suggested = --""2005-01-01"" as that is unambiguous, and ISO
standard.
If you are worried about the double unary obscurity, you could use it with
datevalue

DateValue("2005-01-01")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I like to use:
>
> date(2005,1,1)
> (It's unambiguous)
>
> Then I don't have to worry about datevalue() returning an incorrect date.
>
> does
> =datevalue("01/02/2005")
> refer to Jan 2nd, 2005 or Feb 1st, 2005?
>
> ram wrote:
> >
> > I had to use date value and it works great
> >
> > Range("B27").Value =
> >
Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""01/01/2005""))*(Active!C1:C
24=614546),Active!K1:K24)")
> >
> > Thanks for all the help guys
> >
> > "Bob Phillips" wrote:
> >
> > > That sounds that you don't have a worksheet called ACTIVE
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "ram" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > When I used your code i recevied the #REF! error. Any suggestions?
> > > >
> > > >
> > > > Thanks again
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Try
> > > > >
> > > > > Range("B27").Value =
> > > > > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > > > > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "ram" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > When I use the code below I receive the #value! error message.
Can
> > > someone
> > > > > > explain what i'm doing wrong?
> > > > > >
> > > > > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > > > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > > > > >
> > > > > >
> > > > > > Thanks for your time and help
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson

I don't work with international issues and it still scares me to see
=datevalue("2005-01-01"), especially when =date() exists.

The -- stuff isn't one of my problems <vbg>.

Bob Phillips wrote:
>
> That is why I suggested = --""2005-01-01"" as that is unambiguous, and ISO
> standard.
> If you are worried about the double unary obscurity, you could use it with
> datevalue
>
> DateValue("2005-01-01")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > I like to use:
> >
> > date(2005,1,1)
> > (It's unambiguous)
> >
> > Then I don't have to worry about datevalue() returning an incorrect date.
> >
> > does
> > =datevalue("01/02/2005")
> > refer to Jan 2nd, 2005 or Feb 1st, 2005?
> >
> > ram wrote:
> > >
> > > I had to use date value and it works great
> > >
> > > Range("B27").Value =
> > >
> Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""01/01/2005""))*(Active!C1:C
> 24=614546),Active!K1:K24)")
> > >
> > > Thanks for all the help guys
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > That sounds that you don't have a worksheet called ACTIVE
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "ram" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob,
> > > > >
> > > > > When I used your code i recevied the #REF! error. Any suggestions?
> > > > >
> > > > >
> > > > > Thanks again
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Try
> > > > > >
> > > > > > Range("B27").Value =
> > > > > > Evaluate("=SUMPRODUCT((ACTIVE!A1:A24=--""2005-01-01"")*" & _
> > > > > > "(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24)")
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "ram" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > When I use the code below I receive the #value! error message.
> Can
> > > > someone
> > > > > > > explain what i'm doing wrong?
> > > > > > >
> > > > > > > Range("b27") = Evaluate("=sumproduct((active!A1:A24
> > > > > > > =01/01/2005)*(ACTIVE!C1:C24=614546)*ACTIVE!D1:D24))")
> > > > > > >
> > > > > > >
> > > > > > > Thanks for your time and help
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I don't work with international issues ...

I think that is what the original Excel designers felt!

> The -- stuff isn't one of my problems <vbg>.

No, I know, that was really for the OP's benefit.

Are you saying that the Original Designers knew that I wouldn't have worked with
international issues???

And that you know what my real problems are???

Merry Xmas!

<vbg>

Bob Phillips wrote:
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > I don't work with international issues ...
>
> I think that is what the original Excel designers felt!
>
> > The -- stuff isn't one of my problems <vbg>.
>
> No, I know, that was really for the OP's benefit.

--

Dave Peterson

Blimey, you have to be so careful nowadays<ebg>

And a Merry Xmas to you!

Bob

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Are you saying that the Original Designers knew that I wouldn't have
worked with
> international issues???
>
> And that you know what my real problems are???
>
> Merry Xmas!
>
> <vbg>
>
> Bob Phillips wrote:
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > I don't work with international issues ...
> >
> > I think that is what the original Excel designers felt!
> >
> > > The -- stuff isn't one of my problems <vbg>.
> >
> > No, I know, that was really for the OP's benefit.
>
> --
>
> Dave Peterson