Using AND in a range of cells

[greatsyd]

Is it possible to use AND with a range of cells?

AND OK.pdf

What I would like to achieve is this:

D1 = "ok" if A1 to A9 are all "ok"
Values on column A can be "ok," "not ok"

I can use AND but the problem is, if I have to check 20 cells, my formula will be really long. I was wondering if somebody can help me shorten my formula.

Thank you very much!

[JohnTopley]

Try

=IF(Countif(A1:A9,"OK")=9,"OK","Not OK")

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[greatsyd]

Thanks to John and Shukla.

What if I have more values "ok" to check on the sheet. Say A15 to A20, how would I do that then?

Thanks again!

[AliGW]

Just extend the range and the count in the formula:

=IF(COUNTIF(A1:A15,"OK")=15,"OK","Not OK")

You could also try this:

=IF(COUNTA(A1:A15)=COUNTIF(A1:A15,"OK"),"OK","Not OK")

[greatsyd]

Thanks AliGW.

But what I actually meant was: what if my ranges are separate? Like A1:A9 and then A20:A26?

Thanks!

[AliGW]

OK, so what is the test if the ranges are separate? What are we looking for?

[greatsyd]

2018-03-28_13-21-47.pdf

Please see attached image.

So, D1 = "ok" if A1 to A9 and A13 to A20 are "ok" otherwise D1 = "not ok"

Possible values on A1:A9 and A13:A20 are "ok" and "not ok"

[AliGW]

Try this:

=IF(AND(COUNTA(A1:A9)=COUNTIF(A1:A9,"OK"),COUNTA(A13:A20)=COUNTIF(A13:A20,"OK")),"OK","Not OK")

PS Don't need an image for that - my imagination can run to two arrays of OKs.

[greatsyd]

Great! Thanks a lot!

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.