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.
7,505 22 22 gold badges 58 58 silver badges 87 87 bronze badges asked Feb 27, 2014 at 19:04 141 1 1 gold badge 1 1 silver badge 3 3 bronze badgesCOUNTA will give the number of cells in a range that are not empty.
=IF(NOT(COUNTA(BM2:BQ2)),"","Major Milestone Due")
7,505 22 22 gold badges 58 58 silver badges 87 87 bronze badges
answered Feb 27, 2014 at 19:23
jcfollower jcfollower
352 1 1 silver badge 5 5 bronze badges
The basic formula: NOT(COUNTA(BM2:BQ2)) is applicable with more logical operator, such as: AND(COUNTA(BN2),NOT(COUNTA(BM2:BQ2)))
Commented Jan 7, 2020 at 4:45The 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:
Also, an if - counta will do this no problem as well -
=IF(COUNTA(BM2:BQ2)=0,"","Major Milestone Due")
answered Feb 27, 2014 at 20:06 Raystafarian Raystafarian 21.8k 12 12 gold badges 62 62 silver badges 90 90 bronze badgesThe 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")>
Commented Jan 18, 2018 at 1:31 @MarcusHughes why would that be? It's an array formula and works on the entire array Commented Jan 18, 2018 at 23:07I 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.)
Commented Jan 19, 2018 at 1:29An 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( COUNTBLANK(BM2:BQ2)=5, "", "Major Milestone Due")
=IF( COUNTIF(BM2:BQ2,"")=5, "", "Major Milestone Due")
(Note that COUNTIF(BM2:BQ2,"<>") has the same issue as COUNTA .)