Formula. Retur "OK" if all cells in range contains "OK", ignoring blanks.

[MadBern]

Hi,

I have the following formula working perfectly fine in one cell to return the text "Complete" if all cells in range (G35:G38) contain "Complete": =IF(COUNTA(G35:G38)=0,"Not initiated",IF(COUNTIF(G35:G38,"Complete")=COUNTA(G35:G38),"Complete",IF(COUNTIF(G35:G38,"Not initiated")=COUNTA(G35:G38),"Not initiated","Initiated")))
If all cells contain "Not initated" it returns that and if all contain "Complete" except for one that reads "Initiated", the return is "Initiated".
And it ignores blanks, which is important.

So now I'm trying to get the same function in another cell, but a bit easier. Now the range only has two possible texts, "OK" and "Not OK", and still ignore blanks.
And I cannot get it to work, however I rewrite the formula. The latest iteration returns "OK" if "OK" is found in any of the cells, even tho one or more contain "Not OK".

What am I missing?

 

[AlanY]

are you doing differently?

Book1
	A	B	C	D
1	OK		3	OK
2	OK		4	Not OK
3	OK
4
5	NOT OK
6	NOT OK
7
8	NOT OK
9	NOT OK
Sheet8

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(A:A,"OK")
C2	C2	=COUNTIF(A:A,"NOT OK")

 

[Fluff]

How about
=IF(COUNTIFS(A:A,"Not ok")>0,"Not Ok","Ok")

 

[MadBern]

Hi Fluff

Close.
But if all cells in range are blank, your formula returns "OK". Should be "Not OK" then.

 

[Fluff]

Ok, how about
=IF(AND(COUNTIFS(A:A,"ok"),COUNTIFS(A:A,"Not ok")=0),"Ok","Not Ok")

 

[MadBern]

Ok, how about
=IF(AND(COUNTIFS(A:A,"ok"),COUNTIFS(A:A,"Not ok")=0),"Ok","Not Ok")

Seems to do the trick!
Thanks a bunch!

 

[Fluff]

Glad to help & thanks for the feedback.