I am going bonkers!!!!! I am trying to use SumIf within the confines of the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& ("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried
matching the barackets, checked my ""Quoteation marks

Hi
not tested but try:
xlApp.Range("k20") = Evaluate("=Sumproduct((" & rng1.Address &
"=""F"")*("
& rng2.Address & "<""CLS"")*(" & rng2.Address & "))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I am going bonkers!!!!! I am trying to use SumIf within the confines of the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& ("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've
tried
matching the barackets, checked my ""Quoteation marks

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've
tried
matching the barackets, checked my ""Quoteation marks

Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut in
addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've
tried
matching the barackets, checked my ""Quoteation marks

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is
being used on. Perhaps a different range that holds numbers. I agree that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut in
addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've
tried
matching the barackets, checked my ""Quoteation marks

Sorry gentlemen about the range confusion, I should have checked my typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in my cell.

ANyway, how best to use sumproduct to perform this function. I have only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is
being used on. Perhaps a different range that holds numbers. I agree that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut in
addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS", I've
tried
matching the barackets, checked my ""Quoteation marks

Hi
first try to create the formula directly in a cell (so you have a working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in my
cell.

ANyway, how best to use sumproduct to perform this function. I have only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is
being used on. Perhaps a different range that holds numbers. I agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

I have a formula that works when I enter it into a spreadsheet. I then tried
to insert the variables for the path since the path has several options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " =
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))"

But I can't seem to get it to work and the examples on that great page Frank
sent me to only covers actual A1 type cell refs not those involving the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in my
cell.

ANyway, how best to use sumproduct to perform this function. I have only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is
being used on. Perhaps a different range that holds numbers. I agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I then
tried
to insert the variables for the path since the path has several options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " =
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))"

But I can't seem to get it to work and the examples on that great page
Frank
sent me to only covers actual A1 type cell refs not those involving the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in my
cell.

ANyway, how best to use sumproduct to perform this function. I have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum
is
being used on. Perhaps a different range that holds numbers. I agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select
case statement to set it to various sheet names. The same holds true for the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add using two
arrays. When I try to use three as illustrated in the examples, I'm having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I then
tried
to insert the variables for the path since the path has several options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " =
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))"

But I can't seem to get it to work and the examples on that great page
Frank
sent me to only covers actual A1 type cell refs not those involving the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in my
cell.

ANyway, how best to use sumproduct to perform this function. I have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum
is
being used on. Perhaps a different range that holds numbers. I agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights "CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add using
two
arrays. When I try to use three as illustrated in the examples, I'm having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " =
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))"

But I can't seem to get it to work and the examples on that great page
Frank
sent me to only covers actual A1 type cell refs not those involving the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in
my
cell.

ANyway, how best to use sumproduct to perform this function. I have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what
Sum
is
being used on. Perhaps a different range that holds numbers. I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add using
two
arrays. When I try to use three as illustrated in the examples, I'm having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " =
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))"

But I can't seem to get it to work and the examples on that great page
Frank
sent me to only covers actual A1 type cell refs not those involving the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value in
my
cell.

ANyway, how best to use sumproduct to perform this function. I have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure what
Sum
is
being used on. Perhaps a different range that holds numbers. I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address &
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples, I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " <
CLS))"

But I can't seem to get it to work and the examples on that great
page
Frank
sent me to only covers actual A1 type cell refs not those involving
the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function. I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure
what
Sum
is
being used on. Perhaps a different range that holds numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

I tried your code Bob, and no errors concerning brackets and quotation marks,
but when I run it I get a "#Value" error. Now I have all three ranges set to
be the same size, on the same sheet. When I run the code without the third
argument I get a result.

Any ideas as to where to look for solving this error?

"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples, I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " <
CLS))"

But I can't seem to get it to work and the examples on that great
page
Frank
sent me to only covers actual A1 type cell refs not those involving
the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function. I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure
what
Sum
is
being used on. Perhaps a different range that holds numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

All,

I finally found out what was missing in the SUmproduct equation. The last
argument needed an Ampersand(&), Quotation mark(") and brackets
[' & "))") '] as shown below.

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4 & "))")

Thank you all for your help on this matter, I do not know where else I would
have found this information had it not been for the kindness and patience you
have all shown me. Again, Thanks

Jeff
"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples, I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " <
CLS))"

But I can't seem to get it to work and the examples on that great
page
Frank
sent me to only covers actual A1 type cell refs not those involving
the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function. I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure
what
Sum
is
being used on. Perhaps a different range that holds numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Just a technique. For debugging, instead of

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan ...etc

try

str = "=SUMPRODUCT(--('[Release Plan ....etc

Then in your code:
Debug.Print str

Check the output. Copy this string to the workbook, and enter as an array
formula. This should help w/ debugging.


Another technique might be the following. Instead of vba variable names,
make "CntRef3" a workbook name.

ActiveWorkbook.Names.Add Name:="CntRef3", RefersTo:="Whatever..."


Then
[A1] = [SUMPRODUCT(--(CntRef ="F"),--(CntRef3 <"CLS")....etc ]

--
Dana DeLouis
Win XP & Office 2003


"Jeff" wrote in message
...
I tried your code Bob, and no errors concerning brackets and quotation
marks,
but when I run it I get a "#Value" error. Now I have all three ranges set
to
be the same size, on the same sheet. When I run the code without the third
argument I get a result.

Any ideas as to where to look for solving this error?

"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH


<<snip

Jeff,

That was a tricky one due to the complexity of the formula that made it
difficult for us to test. But, if we were able to get you along the right
lines, and you solve it yourself, even better.

Take a look a Dana's note on debugging techniques in this thread though, the
principles could help you with future problems.

Regards

Bob

"Jeff" wrote in message
...
All,

I finally found out what was missing in the SUmproduct equation. The last
argument needed an Ampersand(&), Quotation mark(") and brackets
[' & "))") '] as shown below.

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4 & "))")

Thank you all for your help on this matter, I do not know where else I
would
have found this information had it not been for the kindness and patience
you
have all shown me. Again, Thanks

Jeff
"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use
your
suggested solution but I keep getting erros which highlight the
brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been
able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding
the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" &
ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" &
ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" &
ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I
use a
select
case statement to set it to various sheet names. The same holds
true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples,
I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" &
ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are
string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet.
I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef
& "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & "
<
CLS))"

But I can't seem to get it to work and the examples on that
great
page
Frank
sent me to only covers actual A1 type cell refs not those
involving
the
substitution of path variables. Does anyone have a thought on
this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you
have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have
checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using
offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of
#Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function.
I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not
sure
what
Sum
is
being used on. Perhaps a different range that holds
numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to
use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips"
schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message

...
I am going bonkers!!!!! I am trying to use SumIf within
the
confines
of
the
Evaluate method. Here is my code;
Set rng1 =
..Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it
Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

Yeah, this is one of those cases where the judicious use of a function
makes life a lot simpler. Not only that, but it will make maintenance
a lot simpler. :)

The following works just fine.

Function fullAddr(rng As Range)
fullAddr = "'[" & rng.Parent.Parent.Name & "]" _
& rng.Parent.Name & "'!" & rng.Address
End Function
Sub testIt()
Dim rng1 As Range, Rng2 As Range, Rng3 As Range
Set rng1 = ActiveSheet.Range("a1:a12")
Set Rng2 = Workbooks("book4").Sheets("sheet1").Range("b1:b12" )
Set Rng3 = ActiveWorkbook.Sheets("sheet3").Range("c1:c12")

MsgBox Evaluate("SUM((" & fullAddr(rng1) & "=""f"")*(" _
& fullAddr(Rng2) & "=""cls"")*" & fullAddr(Rng3) & ")")
End Sub




--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
All,

I finally found out what was missing in the SUmproduct equation. The last
argument needed an Ampersand(&), Quotation mark(") and brackets
[' & "))") '] as shown below.

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4 & "))")

Thank you all for your help on this matter, I do not know where else I would
have found this information had it not been for the kindness and patience you
have all shown me. Again, Thanks

Jeff
"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples, I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " <
CLS))"

But I can't seem to get it to work and the examples on that great
page
Frank
sent me to only covers actual A1 type cell refs not those involving
the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function. I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure
what
Sum
is
being used on. Perhaps a different range that holds numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks

An alternative:

Sub testIt2()
Dim rng1 As Range, Rng2 As Range, Rng3 As Range
Set rng1 = ActiveSheet.Range("a1:a12")
Set Rng2 = Workbooks("book4").Sheets("sheet1").Range("b1:b12" )
Set Rng3 = ActiveWorkbook.Sheets("sheet3").Range("c1:c12")

MsgBox Evaluate("SUM((" & rng1.Address(external:=True) & "=""f"")" _
& "*(" & Rng2.Address(external:=True) & "=""cls"")" _
& "*" & Rng3.Address(external:=True) & ")")
End Sub

(With the sub/end sub lined up! <vbg)





Tushar Mehta wrote:

Yeah, this is one of those cases where the judicious use of a function
makes life a lot simpler. Not only that, but it will make maintenance
a lot simpler. :)

The following works just fine.

Function fullAddr(rng As Range)
fullAddr = "'[" & rng.Parent.Parent.Name & "]" _
& rng.Parent.Name & "'!" & rng.Address
End Function
Sub testIt()
Dim rng1 As Range, Rng2 As Range, Rng3 As Range
Set rng1 = ActiveSheet.Range("a1:a12")
Set Rng2 = Workbooks("book4").Sheets("sheet1").Range("b1:b12" )
Set Rng3 = ActiveWorkbook.Sheets("sheet3").Range("c1:c12")

MsgBox Evaluate("SUM((" & fullAddr(rng1) & "=""f"")*(" _
& fullAddr(Rng2) & "=""cls"")*" & fullAddr(Rng3) & ")")
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
All,

I finally found out what was missing in the SUmproduct equation. The last
argument needed an Ampersand(&), Quotation mark(") and brackets
[' & "))") '] as shown below.

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4 & "))")

Thank you all for your help on this matter, I do not know where else I would
have found this information had it not been for the kindness and patience you
have all shown me. Again, Thanks

Jeff
"Bob Phillips" wrote:

Jeff,

Is it wrap-around?

Try this

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef & _
"=""F""),--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef3 & _
"<""CLS""),('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef4)

--

HTH

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


"Jeff" wrote in message
...
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets
at
he end of the statement.

I have tried to search the web and the newsgroups but I have not been able
to find a solution. I have tried:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)

and I have tried
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Any ideas on where I'm going wrong. I'm having trouble understanding the
Quotation methods.
"Frank Kabel" wrote:

Hi
try:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" &
CntRef4)")

Though if your CntRef are range objects you may have to use the
following:
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" &
ShtRef
& "'!" &
CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef4.address )")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a
select
case statement to set it to various sheet names. The same holds true
for
the
CntRef except that it is set to a range such as "A3:A500"

With everyone's suggestions I've succesfully got sumproduct to add
using
two
arrays. When I try to use three as illustrated in the examples, I'm
having
trouble with the Quotes.
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef
&
"'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" &
CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!"
&
CntRef4)")

I've tried to match the Quotes and the brackets but I still get an
error.
Any pointers?


"Frank Kabel" wrote:

Hi
just tell us what is behind your variables ShtRef, CntRef, etc.
Just as a guess and not tested (assumption your variables are string
variables and not object variables):
msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan
(1,2,3,4).xls]" &
ShtRef & "'!" & CntRef & " =
""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef &
"<""CLS""))")

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I have a formula that works when I enter it into a spreadsheet. I
then
tried
to insert the variables for the path since the path has several
options.

=SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & "
=
F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " <
CLS))"

But I can't seem to get it to work and the examples on that great
page
Frank
sent me to only covers actual A1 type cell refs not those involving
the
substitution of path variables. Does anyone have a thought on this


"Frank Kabel" wrote:

Hi
first try to create the formula directly in a cell (so you have a
working
basis). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"JEff" schrieb im Newsbeitrag
...
Sorry gentlemen about the range confusion, I should have checked
my
typing
better.

Anyway rng2 is different from rng1 and rng3. I am using offset
and
rng
1
to
define rng2 and rng3

When I insert the example shown by Frank I get a error of #Value
in
my
cell.

ANyway, how best to use sumproduct to perform this function. I
have
only
used it to count items in a countif situation.

"Dana DeLouis" wrote:

Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)


Hi Frank. For the op, is rng1 and rng2 the same range? I
might be
wrong,
but it looks like rng1 holds text (ie "=F"), so I'm not sure
what
Sum
is
being used on. Perhaps a different range that holds numbers.
I
agree
that
it looks Sumproduct might help if given the correct ranges.

--
Dana DeLouis
Win XP & Office 2003


"Frank Kabel" wrote in message
...
Hi Bob
I think both of us discovered the same bracket/apostrophe
errors
b
ut
in
addition I think SUMIF won't work. Either the OP has to use
SUMPRODUCT(...
or SUM(IF(....



--
Regards
Frank Kabel
Frankfurt, Germany
"Bob Phillips" schrieb im
Newsbeitrag
...
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref)
xlApp.Range("k20") = Evaluate("SumIF((" &
rng1.Address &
_
"=""F"")*(" & rng2.Address & "<""CLS"")," &
rng2.Address &
"))")
End With

--

HTH

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


"Jeff" wrote in message
...
I am going bonkers!!!!! I am trying to use SumIf within the
confines
of
the
Evaluate method. Here is my code;
Set rng1 = .Worksheets(ShtRef).Range(CntRef)
Set rng2 = .Worksheets(ShtRef).Range(CntRef)
xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address
&
"=""F"")*"&
("
& rng2.Address & "<""CLS")," & rng2.Address & "))")

I keep getting Compile Error:Expected")" and it Highlights
"CLS",
I've
tried
matching the barackets, checked my ""Quoteation marks





















--

Dave Peterson