counting with multiple criteria and wildcards

J

Jorgen Jansson

I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance
 
B

Barb Reinhardt

Try this

=SUMPRODUCT(--(A1:A5="Printed"),--(LEFT(B1:B5,2)="gt"))

Commit with CTRL SHIFT ENTER. You should see {} around it when you are done.

HTH,
Barb Reinhardt
 
M

Max

This might do well enough, based on your sample data as posted:
=SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)="gt"))

A more generic fuzzy to use would be:
=SUMPRODUCT((A1:A5="Printed")*(ISNUMBER(SEARCH("gt",B1:B5))))
 
M

Max

Commit with CTRL SHIFT ENTER. You should see {} around it when you are
done.
Click to expand...

Don't think it's necessary to array-enter, unless TRANSPOSE is used within
the sumproduct
 
J

jeq214

I tried your generic fuzzy approach and it worked perfect, but how would you
count the number of cells that didn't have "gt"?
 

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

count text with * wildcard and date range 1
NEED TO COUNTIF WITH TWO CRITERIA 3
Counting Multiple Records by specific criteria 5
Count if data falls within a range AND that has a certain criteria 1
Sum total using Sumproduct if two criteria met 5
returning text; alternative to sumproduct/sumif? 4
FREQUENCY with multiple criteria 6
How to count occurrences where 2 columns are required to be true? 1

Top