Forum Discussion

Frank Spade's avatar
Frank Spade
Copper Contributor
Apr 09, 2022
Solved

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's avatar
    SergeiBaklan
    Apr 09, 2022

    Frank Spade 

    As variant

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

     

HansVogelaar's avatar
HansVogelaar
MVP
Apr 09, 2022

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)

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Apr 09, 2022
    Why not use COUNTBLANK()?
  • Frank Spade's avatar
    Frank Spade
    Copper Contributor
    Apr 09, 2022
    Thank you.

Resources