Having trouble with IF NOT statments work in Sheets

user_16851383 0
Viewed 37

this might be an easy fix but I just can't figure it out. I'm trying to get this IF statement, if the cell is not FALSE to return value, else to return a certain string. I've tried a couple of ways but I can't make the right combination. And I have a similar issue with excluding the FALSE value from a UNIQUE search statement. This is the sample. Sorry if I'm missing on smth very obvious

google-sheets if-statement
1 Answers

Regarding your concern in setting a value if the cell is NOT FALSE, you can use this formula in Row 2:

=arrayformula(if(A2:A<>"",if(A2:A=FALSE,"Blank",iferror(year(to_date(datevalue(A2:A))),"No year found")),""))

What it does?

  • Check if cell value is FALSE, If yes, set cell value to "Blank", else convert the date string to value using datevalue(). Then use to_date() to convert date value into a date object. Use year() to get the year. Use iferror() to set a default value if the formula encountered an error (when your string is not a valid date string)
  • Loop each row using arrayformula()

Output:

enter image description here

Regarding filtering your data without FALSE:

=filter(A2:A,A2:A <> FALSE)

What it does?

  • Using filter(), filter the data if the cell value is not FALSE

Output:

enter image description here

Note:

You can also use UNIQUE() once you filter your data

=unique(filter(A2:A,A2:A <> FALSE))
user_146060460
Related