Conditional IF Statement with Wildcards

S

SethFlick

Here is a set of sample data and the results I would like to determine...
A1 = Shipped Today
A2 = Shipped Today
A3 = Shipped Yesterday
A4 = Shipped Today
A5 = Shipped Yesterday
A6 = Pending
A7 = Pending
B1 = Red Apples
B2 = Red Apples
B3 = Green Apples
B4 = Red Apples
B5 = Green Apples
B6 = Green Apples
B7 = Green Apples

Total # of Shipments = 5
Formula I used: =countif(A1:A7,"=shipped*")
Works great and I only included it to show the success of a wildcard in a function.

Total # of Shipments of Green Apples = 2
Array Formula that would work if wildcards would work in IF statement:
{=sum(if(A1:A7 = "shipped*", if(B1:B7 = "green*",1,0),0))}
This does not work but I included it to show what I thought would work nicely.

Ultimately, I would like to accomplish this using a single cell if possible.
Thank you in advance for any help on what should be a simple function.
 
H

Harlan Grove

...
...
Total # of Shipments = 5
Formula I used: =countif(A1:A7,"=shipped*")
Works great and I only included it to show the success of a wildcard in a
function.

Total # of Shipments of Green Apples = 2
Array Formula that would work if wildcards would work in IF statement:
{=sum(if(A1:A7 = "shipped*", if(B1:B7 = "green*",1,0),0))}
This does not work but I included it to show what I thought would work nicely.
...

Since you're only looking for text at the beginning of these cells,

=SUMPRODUCT((LEFT(A1:A7,7)="shipped")*(LEFT(B1:B7,5)="green"))
 
D

Dave R.

this should work;

=+SUMPRODUCT((LEFT(A1:A10,4)="ship")*(RIGHT(B1:B10,6="apples")))
 
S

SethFlick

This was exactly the type of simple function I was looking for and had not considered using the sumproduct function. Thank you.
 

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

Top