14

I have a formula where I am checking for a range of cells whether or not they are blank.

If all of them are blank I want to return another blank.

If any of the cells in the range are not blank, I want to return a value Major Milestone Due.

Here is the formula I currently have:

=IF(ISBLANK(BM2:BQ2),"","Major Milestone Due")

It is returning everything as Major Milestone Due, even if there are certain row ranges with all blank cells.

5 Answers 5

19

COUNTA will give the number of cells in a range that are not empty.

So try ...

 =IF(NOT(COUNTA(BM2:BQ2)),"","Major Milestone Due")
1
  • The basic formula: NOT(COUNTA(BM2:BQ2)) is applicable with more logical operator, such as: AND(COUNTA(BN2),NOT(COUNTA(BM2:BQ2)))
    – Rhak Kahr
    Jan 7, 2020 at 4:45
9

Your formula is correct if it is entered as an array formula.

The isblank doesn't work for an array unless you enter it as an array formula by using ctrl + shift + enter. It will look like this by having curly brackets around it:

{=IF(ISBLANK(BM2:BQ2),"","Major Milestone Due")}

Also, an if - counta will do this no problem as well -

=IF(COUNTA(BM2:BQ2)=0,"","Major Milestone Due")

3
  • The array formula isn't quite correct. This version will only go from TRUE to FALSE if the first cell in the range contains a value. In order to correctly check all cells in the range, AND needs to be added -- {=IF(AND(ISBLANK(BM2:BQ2)),"","Major Milestone Due")} Jan 18, 2018 at 1:31
  • @MarcusHughes why would that be? It's an array formula and works on the entire array Jan 18, 2018 at 23:07
  • I don't know the why of it exactly, but I tested it (Excel 2010) and it doesn't work with just ISBLANK. Try entering that formula, keep the first cell in the range blank, then enter data in a later cell in the range.... The ISBLANK condition will still return TRUE. Only if you enter data into the first cell will it return FALSE correctly. Now try adding AND. If any cell in the range gets data added to it, it will return FALSE as it should. (I'm assuming this isn't just some weird fluke with 2010.) Jan 19, 2018 at 1:29
5

An important thing to consider is your definition of "blank". Based on the fact that you used the wording "I want to return another blank", it would appear that you're defining "blank" as cells that appear blank, but which may actually contain a formula that outputs "" and therefore aren't empty.

The COUNTA and ISBLANK methods won't work if you want to handle these sorts of cells as blanks, since those two formulas look for truly empty cells. To handle formulas which output "", you have two options:


  • If you always know the size of your range, you can use either of the following:

    =IF( COUNTBLANK(BM2:BQ2)=5, "", "Major Milestone Due")
    =IF( COUNTIF(BM2:BQ2,"")=5, "", "Major Milestone Due")

    where 5 is the size of your range. This method does not work as well with dynamic ranges.


  • A slightly more complex formula (at least, more complex to explain!) uses SUMPRODUCT:

    =IF( SUMPRODUCT(--(BM2:BQ2<>""))=0, "", "Major Milestone Due")


(Note that COUNTIF(BM2:BQ2,"<>") has the same issue as COUNTA.)

0

Wrap the ISBLANK in an OR or AND function to aggregate the conditions.

Example Image

-1

I arrived at this page in search of the answer to an almost identical question.

When I saw that COUNTA was mentioned, I knew almost immediately what to do. In a slightly different context, I have a range of columns to the left of a column that I want to say not started if all cells in the same row are empty (blank).

The following formula works for me.

=IF(COUNTA(P3:U3)=0,"Not Started"...)

The ellipsis indicate that there are several additional IF functions in my formula, which performs further tests on the range if one or more is populated.

2
  • Welcome to Super User. Noticed your answer attracted a downvote. The likely reason is that we try to avoid duplication. Each answer is intended to provide a solution that is substantially different from what has already been contributed. This essentially duplicates the second part of Raystafarian's answer.
    – fixer1234
    Jul 3, 2016 at 21:09
  • My answer should have been cast as a comment. Nov 6, 2016 at 7:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .