Conditional IF Statement with Wildcards

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
...
...
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.
Click to expand...
...

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

=SUMPRODUCT((LEFT(A1:A7,7)="shipped")*(LEFT(B1:B7,5)="green"))
 
This was exactly the type of simple function I was looking for and had not considered using the sumproduct function. Thank you.
 
Back
Top