Sumproduct with wildcard characters?
 

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:C 500="wb"))

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


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

Sumproduct with wildcard characters?
 

Hi Dan

Try
=SUMPRODUCT(--(Data2!A1:A500='2007'!A13),--(ISNUMBER(FIND("wb",Data2!C1:C500))))--RegardsRoger Govier"Dan" wrote in ...I am 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:C 500="wb")) What is needed is to count "wb-........" Tried below to no avail =SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C 500="wb*")) Any ideas as to how to best relove this? Thank you.

Sumproduct with wildcard characters?
 

That seemed to come out rather garbled on my machine

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


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Dan

Try
=SUMPRODUCT(--(Data2!A1:A500='2007'!A13),--(ISNUMBER(FIND("wb",Data2!C1:C500))))--RegardsRoger
Govier"Dan" wrote in
...I am
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:C 500="wb")) What is
needed is to count "wb-........" Tried below to no avail
=SUMPRODUCT((Data2!A1:A500='2007'!A13)*(Data2!C1:C 500="wb*")) Any
ideas as to how to best relove this? Thank you.

Sumproduct with wildcard characters?
 

Another:

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




Dan wrote:

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:C 500="wb"))

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

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

--

Dave Peterson