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

MadBern

New Member
Joined
May 10, 2019
Messages
35
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?
 

Some videos you may like

Excel Facts

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,234
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
=IF(COUNTIFS(A:A,"Not ok")>0,"Not Ok","Ok")
 

MadBern

New Member
Joined
May 10, 2019
Messages
35
Hi Fluff

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,256
Messages
5,577,006
Members
412,760
Latest member
PetterL
Top