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)
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.
:
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
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?
:
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
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
:
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
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.
:
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
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
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)
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
