ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Evaluate sumproduct question (https://www.excelbanter.com/excel-programming/348684-evaluate-sumproduct-question.html)

Ram
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

Bob Phillips[_6_]
Evaluate sumproduct question
 
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" wrote in message
...
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

Ram
Evaluate sumproduct question
 
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" wrote in message
...
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
Evaluate sumproduct question
 
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" wrote in message
...
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

Bob Phillips[_6_]
Evaluate sumproduct question
 
That sounds that you don't have a worksheet called ACTIVE

--

HTH

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


"ram" wrote in message
...
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" wrote in message
...
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

Ram
Evaluate sumproduct question
 
I had to use date value and it works great

Range("B27").Value =
Evaluate("=SUMPRODUCT((Active!a1:a24=Datevalue(""0 1/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" wrote in message
...
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" wrote in message
...
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
Evaluate sumproduct question
 
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(""0 1/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" wrote in message
...
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" wrote in message
...
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

Ram
Evaluate sumproduct question
 
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(""0 1/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" wrote in message
...
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" wrote in message
...
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

Bob Phillips[_6_]
Evaluate sumproduct question
 
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" wrote in message
...
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(""0 1/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" wrote in message
...
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" wrote in message
...
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
Evaluate sumproduct question
 
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" wrote in message
...
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(""0 1/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" wrote in message
...
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" wrote in message
...
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

Bob Phillips[_6_]
Evaluate sumproduct question
 

"Dave Peterson" wrote in message
...
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
Evaluate sumproduct question
 
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" wrote in message
...
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

Bob Phillips[_6_]
Evaluate sumproduct question
 
Blimey, you have to be so careful nowadays<ebg

And a Merry Xmas to you!

Bob

"Dave Peterson" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 02:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com