if-statementgoogle-sheetsis-empty

if range in column <> x value condition in google sheets

I'm totaling drawing a blank here on how to do this...

             A    
       #1
       #2
       #3
       #4   foo
       #5  
       #6   bar

if any cells A1 through A5 does not equal "foo" then return "" for A6. I will iterate this through all rows to check the rows above.

So in the above example it would return "bar" for A6. If A4 was empty it should return "" for A6

=IF(COUNTA(A1:A5)<>"foo",""

Update: I have managed a workaround using an adjacent column where I put:

=index($A$1:A5="foo",COUNTA($A$1:A5))

Then I can just toggle the true/false output.

Solution

  • You can use the COUNTA function.

    =IF(COUNTA(A1:A5)=0,,<value_if_false>)

    By the way, it's bad practice to return an empty string "", if you want to return no value just leave the argument empty.

    IF(condition,"",value_if_false)   Bad practice   🞬
IF(condition,,value_if_false)     Good practice  ✔