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

Dave Peterson

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

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

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
 
T

Terry Bennett

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top