CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Michael
Michael

Reputation: 59

How to check if the column contains values that are not numbers but also exclude blanks?

Title is pretty much the question, but I'm trying to check if there's an efficient way to check if two columns (not infinite) contain values that are not numbers.

I've tried to use something like

=ARRAYFORMULA(IF(ISNUMBER(A2:A), IF(ISNUMBER(B2:B), "", "errB"), "errA"))

but the problem with this is that it also counts blanks and I feel like expanding the formula by using <> to exclude blanks is inefficient.

any tips and guidance are appreciated!

Upvotes: 1

Views: 1424

Answers (3)

Fredrik Karlsen
Fredrik Karlsen

Reputation: 23

Here is a simple solution:

=COUNTIF(A:B,"><")

enter image description here

Norwegian Sheets - so change ";" with ","

Upvotes: 1

player0
player0

Reputation: 1

if you want to count it you can do:

=COUNTA(IFNA(FILTER(FLATTEN(A:B);
       NOT(ISNUMBER(FLATTEN(A:B))); 
       NOT( ISBLANK(FLATTEN(A:B))))))

enter image description here

Upvotes: 1

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

I understand that you want a formula to check if two cells from different columns are numbers or not. I will assume that you want a TRUE result if both cells are numbers, and FALSE otherwise. This is the formula that fits those requirements:

=AND((IF(ISNUMBER(A2:A), "TRUE", "FALSE"))="TRUE",(IF(ISNUMBER(B2:B), "TRUE", "FALSE"))="TRUE")

I have used the same IF(ISNUMBER()) structure as your example. I only added the AND function to join both checks together. That function returns TRUE if every check inside it returns TRUE.

Upvotes: 1

Related Questions