using wildcard in countifs

[snowman1976]

hello
I have this formula that I use to determine if I can 'kill' a row if it meets the 4 criteria's in a different tab called kill_list.
it works fine, but if I try to apply a wildcard in column B of the kill_list the formula fails. It wants a numeric value in order for it to work

what is the proper wildcard to use so I can apply the formula ? I have tried * and ? and neither work. is there a better method?


IF(COUNTIFS(kill_list!A:A,B17,kill_list!B:B,D17,kill_list!C:C,"<="&K17,kill_list!D:D,">="&K17)>0,"KILL","")

 

[mart37]

kill_list!B:B,"*"&D17&"*"

 

[estevaoba]

Try this:

=[COLOR=#333333]IF(COUNTIFS(kill_list!A:A,B17,kill[/COLOR][B]_list!B:B,"*"&D17&"*",kill_list!C:C,"<="&K17,kill_list!D:D,">="&K17)>0,"KILL","")[/B]

Godspeed!

 

[snowman1976]

thanks, but that isnt quite what I was looking for. i want the wildcard to be in the kill_list, and that is where its failing - my countifs formula won't use it and apply it to any number I have in D17.

Below is the what the kill_list looks like. I want the countifs to meet all the criteria, but some cases I dont care for the tool number and that is where I am trying to use the wildcard but its not working

[TABLE="width: 344"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Line[/TD]
[TD]Tool # to remove[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]25[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Bus[/TD]
[TD]161[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]281[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]283[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]277[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]278[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]285[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]279[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
[TR]
[TD]Truck[/TD]
[TD]*[/TD]
[TD="align: right"]6/12/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[mart37]

"~*"

 

[snowman1976]

this didnt work for me, I even tried to dumb it down to see if a simple countif with the one criteria would work and it still doesnt

 

[mart37]

Maybe you can place an example with data and formula.

 

[snowman1976]

I made a simpler version of the problem below.
In columns A and B is the data set
In D and E is what I am counting, and if I find a match in both cases the formula is TRUE

my countifs formula is this in cell F2:
IF(COUNTIFS(A:A,D2,B:B,E2)>0,TRUE,FALSE)

I want this to come back as TRUE, because I have the wildcard in column B against train...so I am trying to find all cases of Train in this case regardless of the number I am also looking up. However, this is not working and I dont know how to use a wildcard with the countifs, which is what I am trying to figure out. Where I am really applying this is much more data, so just putting an if statement to say =if(D2="train", true) would not work. I hope I explained this well




[TABLE="width: 501"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BUS[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]BUS[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CAR[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]TRAIN[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TRAIN[/TD]
[TD]*[/TD]
[TD][/TD]
[TD]CAR[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PLANE[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try in F2

=IF(SUMPRODUCT(COUNTIFS(A:A,D2,B:B,CHOOSE({1,2},E2,"*")))>0,TRUE,FALSE)

Hope this helps

M.

 

[mart37]

=or(countifs(b:b,e2,c:c,f2),countifs(b:b,e2,c:c,"~*"))