Array formula to check for blanks

1
votes

I have a google sheet with customer orders.

  • Column A contains the order number

  • The next four columns contain various data, filled at various stages of the order. (Columns B through E)

  • In Column F I'd like to have an array formula that checks when the orders are complete, i.e. each column in a row is filled in, for all rows that contain an order number.

Therefore, if A2:E2 are all filled with data, then in F2 it should state "Complete".

I've tried:

COUNTA
ISBLANK
AND
OR
COUNTBLANK

All formula work on a row by row basis, but not when entered in an arrayformula.

=ArrayFormula(if(and(LEN(A2:A),COUNTA($B2:E)=0)="True","Complete","There be blanks afoot")

Or

=ArrayFormula(If(LEN(A3:A),IF(COUNTBLANK($B2:$E)>0,"Blanks","No Blanks"),""))

Test sheet can be found here: https://docs.google.com/spreadsheets/d/1mNIGRh910k_q9J2P6mzv9q-h-me3zxbCWeJ2mcaFsXQ/edit?usp=sharing

1
arraysif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas

1 Answers

1
votes

try:

={"Status"; ARRAYFORMULA(IF(A2:A<>"", IF(MMULT(
 IF(B2:E<>"", 1, 0), {1;1;1;1})=4, "Complete", "Still blanks"), ))}

0