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