Apr 09 2022 11:27 AM - edited Apr 09 2022 11:28 AM
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?
Apr 09 2022 11:52 AM
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)
Apr 09 2022 12:31 PM
SolutionApr 09 2022 12:59 PM - edited Apr 09 2022 01:01 PM
Thank you.
What are the spaces for? It works well without.
Apr 09 2022 01:07 PM
@Frank Spade , you are welcome.
Spaces are for better readability, that's up to you use them or not. Doesn't affect calculations.
Apr 09 2022 01:10 PM
Apr 09 2022 02:01 PM
Apr 09 2022 02:03 PM
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.
Apr 09 2022 12:31 PM
SolutionAs variant
=IF( SUM(--NOT(ISBLANK(A1:AD3) ) ), "have some", "all are blank" )