How do I ignore blank cells in formula?

[MurdochQuill]

I'm using the following formula but I want to exclude blank cells from the countif criteria... "*"&'Sheet10'!$B$5:$B$9&"*"

=IF(OR(INDEX(COUNTIF(F2, "*"&'Sheet10'!$B$5:$B$9&"*"),)), "YES", "NO")

Anyone know how?

 

[Radoslaw Poprawski]

Minisheet please (read my signature)

Also try explaining ur process and expected result better.
its a bit of a guessing game, and there might be easier solution
Like filter function.

 

[MurdochQuill]

Minisheet please (read my signature)

Also try explaining ur process and expected result better.
its a bit of a guessing game, and there might be easier solution
Like filter function.

Xl2bb is crashing for me atm.

Basically It's just a table of criteria on another page.

Criteria 1	Criteria 2	Criteria 3	Criteria 4
DD	CC	BB	AA
HH	JJ	KK	LL
BB	MM	NB	BN

I want to find any matches with the above formula "=IF(OR(INDEX(COUNTIF(F2, "*"&'Sheet10'!$B$5:$B$9&"*"),)), "YES", "NO")"
F2 = either YES/NO if the criteria is returned. F2 is a jumbled mess of data like "DFGKFDJGHBB"

My table of criteria is dotted with blank cells at random, I just want to ignore them & treat it as a "not found", which would return "NO" if no other criteria is found.

I just thought there might be a more elegant way to do it, but I might just put in a nested IF.

 

[Radoslaw Poprawski]

you are looking for a text string within another text string

Try using Search or find functions.

Book5
	F	G	H	I	J	K	L
1	DFGKFDJGHBB		Criteria 1	Criteria 2	Criteria 3	Criteria 4
2			DD	CC	BB	AA
3			HH	JJ	KK	LL
4			BB	MM	NB	BN
5
6			No	No	Yes	No
7			No	No	No	No
8			Yes	No	No	No
9
Sheet1

Cell Formulas
Range	Formula
H6:K8	H6	=IF(IFERROR(FIND($H$2:$K$4,F1,1)>0,FALSE),"Yes","No")
Dynamic array formulas.

 

[MurdochQuill]

you are looking for a text string within another text string

Try using Search or find functions.

Book5
	F	G	H	I	J	K	L
1	DFGKFDJGHBB		Criteria 1	Criteria 2	Criteria 3	Criteria 4
2			DD	CC	BB	AA
3			HH	JJ	KK	LL
4			BB	MM	NB	BN
5
6			No	No	Yes	No
7			No	No	No	No
8			Yes	No	No	No
9
Sheet1

Cell Formulas
Range	Formula
H6:K8	H6	=IF(IFERROR(FIND($H$2:$K$4,F1,1)>0,FALSE),"Yes","No")
Dynamic array formulas.

This seems to give me a spill.

My criteria is done column by column. So I have criteria in column B on sheet 10. But I want to match the exact criteria somewhere within F2 on the current sheet.

=IF(IFERROR(SEARCH('Sheet10'!$B$5:$B$20,F2,1)>0,FALSE),"Yes","No")

 

[Radoslaw Poprawski]

yes, the forumula spills.
otherwise how will you know which text string is contained in f1?

 

[Fluff]

How about

=IF(SUM((Sheet10!B5:B9<>"")*(ISNUMBER(MATCH("*"&Sheet10!B5:B9&"*",F2,0)))), "YES", "NO")