0

I have a column. I want to check with a formula that all the cells in the column are either empty, or contain the string "OK". It looks like the formulas MATCH() or COUNTIF() would not help because they check if any cell contain the string, and I want to check for all cells. Basically the formula should be equivalent to:
(cell1 contains "OK" or is empty) AND
(cell2 contains "OK" or is empty) AND
(cell3 contains "OK" or is empty) AND
etc...

1

I use:

=IF(COUNTA(A:A)=COUNTIF(A:A,"OK"),"All OK","Some Errors")  

COUNTA() returns the number of not empty cells.
The questions is equivalent to:
- all not empty cells contain "OK"

0

Try this:

=IF(ROWS(A:A)<>COUNTIF(A:A,"OK")+COUNTBLANK(A:A),"All not OK","All OK")

It will count rows in column A and see if it is the same as all rows with "OK" and blanks.

enter image description here

  • Yes that works fine, thanks for leading me to the answer. (See a shorter version below) – Bab Oct 18 '17 at 10:43

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.