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

##### New Member
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?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### AlanY

##### Well-known Member
are you doing differently?

Book1
ABCD
1OK3OK
2OK4Not OK
3OK
4
5NOT OK
6NOT OK
7
8NOT OK
9NOT OK
Sheet8
Cell Formulas
RangeFormula
C1C1=COUNTIF(A:A,"OK")
C2C2=COUNTIF(A:A,"NOT OK")

#### Fluff

##### MrExcel MVP, Moderator
=IF(COUNTIFS(A:A,"Not ok")>0,"Not Ok","Ok")

##### New Member
Hi Fluff

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

#### Fluff

##### MrExcel MVP, Moderator

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

##### New Member
=IF(AND(COUNTIFS(A:A,"ok"),COUNTIFS(A:A,"Not ok")=0),"Ok","Not Ok")
Seems to do the trick!
Thanks a bunch!

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
1
Views
413
Replies
9
Views
84
Replies
5
Views
74
Replies
3
Views
341
Replies
9
Views
896