Which g-sheet formula can I use to check if a column has only empty or NA values?
3 Answers
You can try this formula below:
It returns 1 if cell is NA or blank.
=AND(ARRAYFORMULA(ISNA(A2:A) + ISBLANK(A2:A)))
This checks if an entire column(except for the header) is blank.
Based on my understanding, you are trying to check if a column or a one-columned-range to downward from a specific cell is consist of only blank or #N/A errors.
I'd solve this by counting 1. all cells, 2. blank cells, and 3. #N/A cells in the range, and comparing 1 vs 2 + 3.
For 1. all cells, we can use ROWS([RANGE]). Assuming the Column range as [D2:D], the formula will be =ROWS(D2:D)
For 2. blank cells, we have a function which count blank. the formula will be =COUNTBLANK(D2:D)
For 3. #N/A cells, we need to check if each cell is #N/A and count it. The formula will be =COUNTIF(ARRAYFORMULA(ISNA(D2:D)), TRUE)
In conclusion, the checking formula will be =ROWS(D2:D) = COUNTIF(ARRAYFORMULA(ISNA(D2:D)),TRUE) + COUNTBLANK(D2:D)
Of course, if you wanted to check this for a filtered sheet, this method will not be adequate but that was not included in your question and I assumed it's not your situation.
You can use a simple construction:
=COUNTA(INDEX(IFNA(C:C)))
where C:C is the column of interest.
If you need one text or another depending if there is any content:
=IF(COUNTA(INDEX(IFNA(C:C))), "There is some content", "All blanks and #N/As")
Related