Sunday, November 16, 2014

worksheet function - In Excel how do I check that all cells in a range contain only a specific string



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...


Answer



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"


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...