samedi 30 novembre 2019

Array Formula to Check for Blanks

I have a google sheet with customer orders on.

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

Any help appreciated.

Aucun commentaire:

Enregistrer un commentaire