Sumproduct and finding partial strings

[Jeff]

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

 

[Tom Ogilvy]

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.

 

[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*")

 

[Jeff]

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.

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

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.

 

[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

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.

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

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.