dont use formula if cell range is blank

**bez** · 08-07-2012, 08:01 AM

Column D2:D9 results D10with formula (SUM(D2:D7)-(SUM(D8:D9))) thus gives a 0 IN D10

now in D11 ive put a formula in
=IF(AND(ISBLANK(D2:D9)),"",IF(D10>0,3,0)+IF(D10<0,0,0)+IF(D10=0,1,0))

IF I CLEAR CONTENTS OF D2:D9 IT STILL LEAVES A 0 IN D1O (which i would expect)
because the 0 is in D10, the formula in D11 says then it value is 1, as read from the formula in D11.
i obviosly have got wrong formula in D11.
therefore what i require is D11 to be left blank until range D2:D9 has had some input

where am i going wrong ?

**benishiryo** · 08-07-2012, 08:31 AM

hi bez, welcome to the forum. because ISBLANK is for a single cell & you have put a range. to make it work, you have to make it an array formula. which means you have to click inside the formula bar (or press F2 in that cell) & press CTRL + SHIFT + ENTER

**bez** · 08-07-2012, 08:42 AM

Fantastic, it worked

**Cutter** · 08-07-2012, 10:06 AM

@ bez

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.