Sumproduct and finding partial strings

I am using the SUmPRoduct and Evalute method to count up vlaues based on
conditions. THe problem I'm having is I cannot seem to be able find the right
syntax for finding a partial piece of info. For example I would like to find
and count the entires which begin with "SME". Normally I might use find or a
wildcard, but neither seem to work. I have tried looking at Chip's site as
well as the enormously helpful XLDYNAMIC site, but no success.

This is my codeor more purposely the snippet I'm concerned with)
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
"=""SME""))")

Where in good gods name do I get it to find partials . . . HELP

xlApp.Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")

test out the string portion in the immediate window

shtRef = "Sheet1"
set CntRef5 = Range("B9:B50")
? ("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")
' which produces the string
=SUMPRODUCT(--(LEFT('[Release Plan
(1,2,3,4).xls]Sheet1'!$B$9:$B$50,3)="SME"))

Now Test it with Evaluate
? Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
ShtRef & "'!" & CntRef5.Address & _
",3)=""SME""))")
'Produces
4

Which is correct in my sheet.

--
Regards,
Tom Ogilvy

Note that for a single condition like this, SUMIF will work and is easier

set rng = Worksheets("Release Plan (1,2,3,4).xls").Worksheets( _
ShtRef).Range(CntRef5.Address)
MyCount = xlApp.Sumif(rng,"SME*")


or if CntRef5 is the actual range

MyCount = xlApp.Sumif(CntRef5,"SME*")

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> xlApp.Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> ShtRef & "'!" & CntRef5.Address & _
> ",3)=""SME""))")
>
> test out the string portion in the immediate window
>
> shtRef = "Sheet1"
> set CntRef5 = Range("B9:B50")
> ? ("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> ShtRef & "'!" & CntRef5.Address & _
> ",3)=""SME""))")
> ' which produces the string
> =SUMPRODUCT(--(LEFT('[Release Plan
> (1,2,3,4).xls]Sheet1'!$B$9:$B$50,3)="SME"))
>
> Now Test it with Evaluate
> ? Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> ShtRef & "'!" & CntRef5.Address & _
> ",3)=""SME""))")
> 'Produces
> 4
>
> Which is correct in my sheet.
>
> --
> Regards,
> Tom Ogilvy
>
>
>

Thanks TOm I'll give it a try. The snippit I showed in my request was just
one part of a 5 condition sumproduct statement.

Is there a good book or advanced tutorial on all the quirks of Sumproduct. I
love it as a function, but I am having a bit of trouble getting the operators
and conventions down.

"Tom Ogilvy" wrote:

> Note that for a single condition like this, SUMIF will work and is easier
>
> set rng = Worksheets("Release Plan (1,2,3,4).xls").Worksheets( _
> ShtRef).Range(CntRef5.Address)
> MyCount = xlApp.Sumif(rng,"SME*")
>
>
> or if CntRef5 is the actual range
>
> MyCount = xlApp.Sumif(CntRef5,"SME*")
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > xlApp.Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > ShtRef & "'!" & CntRef5.Address & _
> > ",3)=""SME""))")
> >
> > test out the string portion in the immediate window
> >
> > shtRef = "Sheet1"
> > set CntRef5 = Range("B9:B50")
> > ? ("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > ShtRef & "'!" & CntRef5.Address & _
> > ",3)=""SME""))")
> > ' which produces the string
> > =SUMPRODUCT(--(LEFT('[Release Plan
> > (1,2,3,4).xls]Sheet1'!$B$9:$B$50,3)="SME"))
> >
> > Now Test it with Evaluate
> > ? Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > ShtRef & "'!" & CntRef5.Address & _
> > ",3)=""SME""))")
> > 'Produces
> > 4
> >
> > Which is correct in my sheet.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
>
>
>

Sumproduct is just a convenience. What you are actually doing is an array
formula. Sumproduct allows you to enter some types of array formulas
without use array type entry (ctrl+Shift+Enter). So what you are looking
for is information on Array formulas. You should probably ask the question
on tutorials or documentation over in worksheet.functions. Aladin Arydik
(sp) often posts a reference to a long explanation he gave to this in Mr.
Excel (I believe).
http://www.mrexcel.com/wwwboard/messages/8961.html

Chip Pearson
http://www.cpearson.com/excel/array.htm

Bob Umlas wrote a white paper on them. I think Bob Philips (who you have
been working with) has information on his site.

--
Regards,
Tom Ogilvy


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Thanks TOm I'll give it a try. The snippit I showed in my request was just
> one part of a 5 condition sumproduct statement.
>
> Is there a good book or advanced tutorial on all the quirks of Sumproduct.
I
> love it as a function, but I am having a bit of trouble getting the
operators
> and conventions down.
>
> "Tom Ogilvy" wrote:
>
> > Note that for a single condition like this, SUMIF will work and is
easier
> >
> > set rng = Worksheets("Release Plan (1,2,3,4).xls").Worksheets( _
> > ShtRef).Range(CntRef5.Address)
> > MyCount = xlApp.Sumif(rng,"SME*")
> >
> >
> > or if CntRef5 is the actual range
> >
> > MyCount = xlApp.Sumif(CntRef5,"SME*")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > xlApp.Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > > ShtRef & "'!" & CntRef5.Address & _
> > > ",3)=""SME""))")
> > >
> > > test out the string portion in the immediate window
> > >
> > > shtRef = "Sheet1"
> > > set CntRef5 = Range("B9:B50")
> > > ? ("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > > ShtRef & "'!" & CntRef5.Address & _
> > > ",3)=""SME""))")
> > > ' which produces the string
> > > =SUMPRODUCT(--(LEFT('[Release Plan
> > > (1,2,3,4).xls]Sheet1'!$B$9:$B$50,3)="SME"))
> > >
> > > Now Test it with Evaluate
> > > ? Evaluate("=SUMPRODUCT(--(LEFT('[Release Plan (1,2,3,4).xls]" & _
> > > ShtRef & "'!" & CntRef5.Address & _
> > > ",3)=""SME""))")
> > > 'Produces
> > > 4
> > >
> > > Which is correct in my sheet.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> >
> >
> >