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...
3 Answers
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"
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.
-
Yes that works fine, thanks for leading me to the answer. (See a shorter version below)– BabOct 18, 2017 at 10:43
A pretty simple mechanism would be to concatenate (using TEXTJOIN()
with no delimiter nowadays) all the cells in the range of interest. Wrap the concatenation in a SUBSTITUTE()
function that substitutes the string of text with "".
If anything except that string is in the cells of the range, it will show as the result of the formula. If all was good (string present, only, or blank) the result will be a blank. One could wrap that in a LEN()
function to make sure the result is not a space or two which would look like a blank result so giving a false success. A length of anything >0 would be that kind of result. A 0 would mean all was as desired.
-
Interesting approach. Would be a better answer with a fully worked example.– Alex MFeb 13, 2020 at 20:37