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