| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =SUMPRODUCT() Question | Excel Discussion (Misc queries) | |||
| Sumproduct question | Excel Discussion (Misc queries) | |||
| Why won't Sumproduct funciton evaluate this data | Excel Worksheet Functions | |||
| A simple question -- Evaluate() | Excel Programming | |||
| Evaluate - Sumproduct GRRR | Excel Programming | |||
Linear Mode
