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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,203
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
48,051
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
48,051
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
48,051
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,879
Members
410,643
Latest member
sng
Top