# Wildcards in SUMPRODUCT

T

#### Terry Bennett

Can anyone help with this please?

I have 3 columns of data and wish to analyse the number of occasions on
which criteria in Columns B and C are met. For example:

- Column B lists types of vehicle
- Column C lists whether or not the vehicle is roadworthy

The complicating issue (from my point of view) is that I need to include
specific words within cells in Column B within the count, ie; types of
vehicles in B will be, for example, "Leyland Olympians", "Volvo Olympians",
etc. In the calculation I need the SUMPRODUCT result to show not only the
number of cases where Leyland Olympians and Volvo Olympians are roadworthy
(that's easy enough) but when all Olympians are rodaworthy. I have
simplified the example - I don't want to merely add the 2 figures together
as there are many different variables beyond the 2 I have listed.

I have tried:

=SUMPRODUCT((\$B\$2:\$B\$14="*Olympian*")*(\$C\$2:\$C\$14="R"))

but this doesn't return the correct result. It seems that COUNTIF can
handle wildcards but SUMPRODUCT can't?

Any suggestions would be welcomed!

Many thanks.

=SUMPRODUCT(--isnumber(search("Olympian",\$B\$2:\$B\$14)),--(\$C\$2:\$C\$14="R"))

=find() is case sensitive.
=search() is not case sensitive.

Hi,

A small variant of the SUMPRODUCT() function suggested by Dave

SUMPRODUCT(ISNUMBER(SEARCH("Olympian",A2:A6,1))*(B2:B6="Y"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Many thanks guys

Dave Peterson said:
=SUMPRODUCT(--isnumber(search("Olympian",\$B\$2:\$B\$14)),--(\$C\$2:\$C\$14="R"))

=find() is case sensitive.
=search() is not case sensitive.