Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

array formula is not working in google sheet

What I want to do:

  • Check if 4 of my cells are blank or not
  • If all of them are not blank, then its okay, else display nothing ("")
  • I want it to be auto dragged down

What I have tried

=arrayformula(if(AND(not(A1:D=""),not(B1:B=""),not(C1:C=""),not(D1:D="")),"ok",""))

Result

  • Although the statements of AND is true, it displays nothing ""

What can be the issue in the formula?

Solution

  • When we use AND() , OR() it won't work with array, I'm not sure why

    But we can count true/false as a number of 1 = true and 0 = false 

    =arrayformula(if(not(A1:A="")+not(B1:B="")+not(C1:C="")+not(D1:D="")>3,"ok",))