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

Line	Tool # to remove	Start Date	End Date
Car	25	1/1/2017	12/31/2017
Bus	161	1/1/2017	12/31/2017
Van	281	1/1/2017	12/31/2017
Van	283	1/1/2017	12/31/2017
Van	277	1/1/2017	12/31/2017
Van	278	1/1/2017	12/31/2017
Van	285	1/1/2017	12/31/2017
Van	279	1/1/2017	12/31/2017
Truck	*	6/12/2017	12/31/2017

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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

	A	B	C	D	E	F
1	BUS	1		BUS	1	TRUE
2	CAR	5		TRAIN	10	FALSE
3	TRAIN	*		CAR	10	FALSE
4	PLANE	10

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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,"~*"))