Sumproduct with wildcard characters?

[Guest]

I am using the following formula successfully as long as the criteria is
exact. The challenge is that the service request description field begins
with the specific indentifiers that identify what type of service request was
resolved in the case below "wb" standing for "walk bys" them followed by
further details. Using Countifs I can use a wildcard "wb*" and it works
perfectly. I am using Office 2007 and the problem is that Countifs is not
supported in Office 2003.

What works if data is only "wb"
=SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C500="wb"))

What is needed is to count "wb-........" Tried below to no avail
=SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C500="wb*"))


Any ideas as to how to best relove this? Thank you.

 

[Roger Govier]

Hi Dan

Try
=SUMPRODUCT(--(Data2!A1:A500='2007'!A13),--(ISNUMBER(FIND("wb",Data2!C1:C500))))--RegardsRoger Govier"Dan" <[email protected]> wrote in messageam using the following formula successfully as long as the criteriais> exact. The challenge is that the service request description fieldbegins> with the specific indentifiers that identify what type of servicerequest was> resolved in the case below "wb" standing for "walk bys" them followedby> further details. Using Countifs I can use a wildcard "wb*" and itworks> perfectly. I am using Office 2007 and the problem is that Countifs isnot> supported in Office 2003.>> What works if data is only "wb"> =SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C500="wb"))>> What is needed is to count "wb-........" Tried below to no avail> =SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C500="wb*"))>>> Any ideas as to how to best relove this? Thank you.

 

[Roger Govier]

That seemed to come out rather garbled on my machine

Try
=SUMPRODUCT(--(Data2!A1:A500='2007'!A13),
--(ISNUMBER(FIND("wb",Data2!C1:C500))))

 

[Dave Peterson]

Another:

=SUMPRODUCT(--(Data2!A1:A500='2007'!A13),--(left(Data2!C1:C500,3)="wb-"))