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

[Frank Spade]

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?

[#2]

@Frank Spade 

In Excel in Microsoft 365 and Office 2021, the formula ISBLANK(A1:AD3) returns an array the same size as A1:AD3.

If you just want to know if there is at least one empty cell in the range, you can use

=COUNTIF(A1:AD3,"")>0

This will return TRUE if at least cell is blank, FALSE otherwise.

If you want to know how many cells are blank, use

=COUNTIF(A1:AD3)

[#3 · Accepted solution]

@Frank Spade 

As variant

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

[#4]

Thank you.

What are the spaces for? It works well without.

[#5]

Thank you.

[#6]

@Frank Spade , you are welcome.

Spaces are for better readability, that's up to you use them or not. Doesn't affect calculations.

[#7]

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

[#8]

Why not use COUNTBLANK()?

[#9]

@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.