How to count multiple rows with ISNUMBER

[DCW40]

I was not able to find an answer anywhere, so now I wondering if my approach is wrong. This is the formula I am using =IF(ISNUMBER(SEARCH("pass",$J$1)),"pass",IF(ISNUMBER(SEARCH("fail",$J$1)),"fail","")). This formula does work but now I need it to read row G through J. It needs to be able to find pass or fail in the rows, but I was using the search function because the cells do not simply say pass or fail there are several options, for example one pass option is payment-pass. I have tried everything I can think of to get my formula to count the additional rows with no luck.

 

[jtakw]

Hi,

What if, within your range G1:J1, both "Pass" and "Fail" is found?

 

[DCW40]

It should only pass if all options pass, if anything fails the result should be fail.

 

[Aladin Akyurek]

Maybe just:

=IFCOUNTIFS(G1:J1,"pass")=COUMNS(G1:J1),"pass","fail")

 

[jtakw]

It should only pass if all options pass, if anything fails the result should be fail.

Hi,

Would it be possible that not All 4 columns Pass, but there is no Fail neither?
Assuming the 4 columns will Always be Pass or Fail:

PLEASE NOTE: This is an array formula, follow instructions below.

Book1
	G	H	I	J	K	M
1	Payment-Pass	Payment-Pass	Payment-Pass	Fail		Fail
2	Payment-Pass	Payment-Pass	Payment-Pass	Pass		Pass
3	Payment-Fail	Fail	Payment-Pass	Pass		Fail
Sheet11

Cell Formulas
Range	Formula
M1	{=IF(COUNT(SEARCH("Pass",G1:J1))=4,"Pass","Fail")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jonmo1]

Maybe just:

=IFCOUNTIFS(G1:J1,"pass")=COUMNS(G1:J1),"pass","fail")

I think you need wildcards in there, OP says the word pass or fail will be among other text
=IFCOUNTIFS(G1:J1,"*pass*")=COUMNS(G1:J1),"pass","fail")

 

[jtakw]

Might as well fix the typos too for Aladin, plus a shortened version:

Book1
	G	H	I	J	K	Q	R
1	Payment-Pass	Payment-Pass	Payment-Pass	Fail		fail	Fail
2	Payment-Pass	Payment-Pass	Payment-Pass	Pass		pass	Pass
3	Payment-Fail	Fail	Payment-Pass	Pass		fail	Fail
Sheet11

Cell Formulas
Range	Formula
Q1	=IF(COUNTIFS(G1:J1,"*pass*")=COLUMNS(G1:J1),"pass","fail")
R1	=IF(COUNTIF(G1:J1,"*Pass*")=4,"Pass","Fail")

 

[Aladin Akyurek]

Might as well fix the typos too for Aladin, plus a shortened version:[...]

Great request. I'm impressed, but less so with the shortened version:

=IF(COUNTIF(G1:J1,"*Pass*")=4,"Pass","Fail")


requires adjusting that 4 whenever G1:J1 changes. While

=IF(COUNTIFS(G1:J1,"*pass*")=COLUMNS(G1:J1),"pass","fail")


needs not.​

​

 

[DCW40]

Thank you so much for helping me! Works just like I need it to!

 

[DCW40]

Is there also a way show a blank cell if no data has been selected?