Need solution for wildcard use in array

[Another wildcard and array problem]

I need to find a solution for the following formula to be used in an Excel
worksheet in Excel 2003:

=SUM((('GCP Vendors'!L3:L999)="*Other*")*(('GCP Vendors'!O3:O999)=
"Consultation"))

I need to count the number of times a cell contains the partial string
"Other" in the L column and then sum up how many of these strings are related
to "Consultation" in the O column. I can't figure out how to substitute for
"*Other*" in an array.

Thanks,
GP

 

[Dave Peterson]

One way is to use
=isnumber(search(...))

=SUMPRODUCT(--(ISNUMBER(SEARCH("other",'gcp vendors'!L3:L999))),
--('gcp vendors'!O3:O999>="Consultation"))

Using =sumproduct() means that I don't have use ctrl-shift-enter to enter the
formula.

And I like the -- and commas syntax.

But

=SUM((ISNUMBER(SEARCH("other",'gcp vendors'!L3:L999))),
*('gcp vendors'!O3:O999>="Consultation"))

will work as an array formula.

Ps. If you want to match case, then use =find() instead of =search().

 

[Tom Hutchins]

Try

=SUMPRODUCT(--(ISNUMBER(FIND("Other",L3:L999))),--(O3:O999="Consultation"))

Hope this helps,

Hutch