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:


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


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

Ashish Mathur


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



Ashish Mathur
Microsoft Excel MVP

Terry Bennett

Many thanks guys

Dave Peterson said:

=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

Sumproduct Help! 2
Sumproduct Wildcards 5
Expanded sumproduct 11
SUMPRODUCT on filtered rows 6
Sumproduct? 3
Excel Countifs/Sumproduct with mutil Or statement 3
Countif/SumProduct 4
Sumproduct in matrix means 3