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

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

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

(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

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