MrExcel Publishing
Your One Stop for Excel Tips & Solutions

ISBLANK DOESN'T GIVE CORRECT


Posted by mark cheatum on September 19, 2001 7:45 AM

AM TRYING TO CREATE A SHEET THAT SUMS OF B3:B33 ARE ADDED TOGETHER, BUT IF NOTHING IS IN THOSE CELLS LEAVE B34 TOTALLY CELL BLANK. THIS IS WHAT I HAVE NOW AND IT IS NOT RIGHT.

=IF(isblank," ","SUM(B3:B33)")

CAN SOMEONE HELP, AM LEARNING EXCEL WAS A LOTUS MAN FOR YEARS.


Posted by mark cheatum on September 19, 2001 7:24 AM

TRIED THE CTRL-SHIFT-ENTER GOT THE ARRAY ENDINGS, BUT IT STILL SHOW UP IN SLOT B34 WHERE FORMULA IS AS THE FORMULA ITSELF, NOT A BLANK AND YOU ADD ANY NUMBER IN 3-33 THEY DO NOT ADD INTO 34 TOTAL.
IN 34 IT IS ###### INSTEAD OF BLAN OR NUMBER TOTAL OF B3:B33.


Posted by Mark W. on September 19, 2001 7:26 AM

What's the cell format applied to B34? If it is
"Text" then change it to "General" and re-enter
the array formula as previously described.


Posted by mark cheatum on September 19, 2001 7:48 AM

thanks for the help.

Posted by Mark W. on September 19, 2001 7:49 AM

{=IF(AND(ISBLANK(B3:B33)),"",SUM(B3:B33))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Eric on September 19, 2001 8:02 AM

Keep in mind that the result appears blank, but actually contains a formula- just FYI (NT)

Posted by mark cheatum on September 19, 2001 8:09 AM

TRIED THE CTRL-SHIFT-ENTER GOT THE ARRAY ENDINGS, BUT IT STILL SHOW UP IN SLOT B34 WHERE FORMULA IS AS THE FORMULA ITSELF, NOT A BLANK AND YOU ADD ANY NUMBER IN 3-33 THEY DO NOT ADD INTO 34 TOTAL.
IN 34 IT IS ###### INSTEAD OF BLAN OR NUMBER TOTAL OF B3:B33.