THank you Tom for not only providing an answer, but taking the time to
explain and provide some alternatives. You have gone beyond the expected.I
am sure I can put it together for a solution now.

"Tom Ogilvy" wrote:

(First, the sumproduct formula won't give you what you want, but for
demonstration purposes, I will continue using it to demo:)

No, i am using insert=Name=Define (named ranges/defined names) which is
what I thought you said you wanted to use. if rnge is a range reference as
in

set rngE = Columns(1)
or set rngE = Range("E:E")

then that won't work.
A) because you can't use an array formula on a whole column in xl2003 and
earlier
b) you can not use a range reference variable directly - you must use the
address it refers to

if rngE and rngI are defined names less than a full column:

Dim s as String, res as Variant
s =
"Sumproduct(--(rngE={""AIE"",""AMS"",""Aut""}),--(rngI={""Closed"",""Submitted""}))"
res = Evaluate(s)

If instead rngE and rngI are

Dim rngE as Range, rngI as Range
Dim s as String, res as Variant
set rngE = Range("E2:E" & LastRow)
set rngI = Range("I2:I" & LastRow)
s = "Sumproduct(--(" & rngE.Address & _
"={""AIE"",""AMS"",""Aut""}),--(" & _
rngI.Address & "={""Closed"",""Submitted""}))"
res = Evaluate(s)

Or

Dim s as String, res as Variant
s = "Sumproduct(--(E1:E" & LastRow & _
"={""AIE"",""AMS"",""Aut""}),--(I1:I" & _
LastRow & "={""Closed"",""Submitted""}))"
res = Evaluate(s)


You can really only use 1 multiple condition condition. You might have use
to sumproduct formulas to check teh 3 entries agains closed and the 3
entries against submitted.

this works in the worksheet
=SUMPRODUCT((E1:E10={"AIE","AMS","Aut"})*(I1:I10=" Closed"))+SUMPRODUCT((E1:E10={"AIE","AMS","Aut"})* (I1:I10="Submitted"))

s1 = "Sumproduct(--(E1:E" & LastRow & _
"={""AIE"",""AMS"",""Aut""}),--(I1:I" & _
LastRow & "=""Closed""))
s2 = "Sumproduct(--(E1:E" & LastRow & _
"={""AIE"",""AMS"",""Aut""}),--(I1:I" & _
LastRow & "=""Submitted""))

res = Evaluate(s1 & "+" & s2)

--
Regards,
Tom Ogilvy



"Wiley" wrote in message
...
Tom,

Thanks for the help. However, it lloks like you are using cell reference
for the values. Due to refresh of the data, I am trying to avoid helper
cells. Can I do it something like this? (where rngE is column E and rngI
is
column I)

s = "Sumproduct(--(rngE=""AIE"",""AMS"",""Aut""
),--(rngI=""Closed"",""Submitted""))"

res = Evaluate(s)

It does compile fine. However, I get a Type Mismatch on the 's' lline if
"s" and "res" are declared as a long. If I change both to strings, then I
get mismatch on the Evaluat line. WHat should I be using?

I have varaibles for the last row. So I can do "E2:E" & LAastRow), but I
cannot seem to get that to work in SUMPORDUCT either.In case that is any
easier.


"Tom Ogilvy" wrote:

Yes it will work

s = "Sumproduct(--(myrng=A1),--(myrng1=B1),--(myrng2=C1),myrng3)"

res = Evaluate(s)

would work with defined names myrng, myrng1, myrng2, myrng3

--
Regards,
Tom Ogilvy


"Wiley" wrote in message
...


I posted somehting like this last week, but the asnwe Irecieved was to
use
SUMPRODUCT. Firs I am filtering the record set to match a date range.
THen
on that subset, I must use any of 3 to 10 different criteria in 2
columns
to
determine if a record is counted. So if a record matches one of the
values
in COlE and one of the picked vale in COl I, then it gets counted. I
need
to
do this in VBA. Iif I use the EVALUATE, I can use the formula in code.
However, it will not work with name ranges (at least for me). Does
anyone
know if you can use a named vairable range in SUMPODUCT instead of
literal
cell references. This needs to be a repeatable process on data that
will
get
refereshed and willnot have the same number of rows. I am not real
stroing
in VBA.

Thanks