How to determine if there is a non-empty cell in a range

I have tried for over an hour to find a solution to this simple question and finally give up. ISBLANK(A1:AD3) gave an overflow warning. Can somebody please tell me the answer?

excel

Formulas and Functions

SergeiBaklan

Apr 09, 2022

Frank Spade

As variant

=IF( SUM(--NOT(ISBLANK(A1:AD3) ) ), "have some", "all are blank" )

Frank Spade

Copper Contributor

Apr 09, 2022

Sorry, it looks weird to me. Who said, its better readable? I didn't learn it at the university.
Thanks anyway.

SergeiBaklan

Diamond Contributor

Apr 09, 2022

Frank Spade

Again, that's your choice how to write formulas,

such way

=LAMBDA(Array1,Array2,Defaults,LET(R,ROWS(Array1)+ROWS(Array2),C,MAX(COLUMNS(Array1),COLUMNS(Array2)),D,IF(COLUMNS(Defaults)>1,INDEX(Defaults,1,0),LEFT(INDEX(Defaults,1,1),SEQUENCE(1,C,LEN(INDEX(Defaults,1,1)),0))),X,SEQUENCE(1,C),Y,SEQUENCE(R),IF(Y<ROWS(Array1)+1,IFERROR(INDEX(Array1,Y,X),IFERROR(INDEX(D,1,X),"")),IFERROR(INDEX(Array2,Y-ROWS(Array1),X),IFERROR(INDEX(D,1,X),"")))))

or like

=LAMBDA(arrayA, arrayB,
    HSTACK(
        TOCOL( IF(SEQUENCE(, ROWS(arrayB)), arrayA)),
        TOCOL( IF(SEQUENCE(, ROWS(arrayA)), arrayB), , 1)
    )
)

If you consider that first way is much better - that's okay. As far as that's okay for people with whom you share your formulas.

Forum Discussion

How to determine if there is a non-empty cell in a range

Resources