Wildcards in SUMPRODUCT

[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.

 

[Dave Peterson]

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

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

 

[Ashish Mathur]

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

 

[Terry Bennett]

Many thanks guys

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

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