IF function for blank cell

B

bayanbaru

My formula is C1=SUM(A1: B1). I want to use IF function. If A1 and B1 is
blank C1 should be blank. Right now it show C1=0
 
D

David McRitchie

If the question was correct then that should be an AND
and you might have to use SUM if either A1 or B1 is empty

=IF(AND(a1="",B1=""),"",SUM(A1:B1))

sometimes people put a space into a cell or a null string simiar to what is
being entered into Cell C1 with ="", so you might have to consider
=IF(AND(TRIM(A1)="",TRIM(B1)=""),"",SUM(A1:B1))

if the cell is actual empty, tests true for =ISBLANK(A1), then would work for A1+B1
but a cell with a null string would fail with a #VALUE! error..
SUM will ignore text constants.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
M

macropod

Hi bayanbaru,

If you only want to hide the zero values:
.. throughout a worksheet, you could use Tools|Options|View and uncheck the
zero values option
.. in a single cell or a range of cells, you could apply a custom format.

For the latter, you could:
.. use Format|Cells|Number to apply the formatting you want for *both* +ve
and -ve values (i.e. the formatting code must include a semi-colon (i.e.
';') to distinguish +ve and -ve values) , then
.. click 'Custom' and add a semi-colon to the end of the formatting code (eg
'#,##0.00_ ;-#,##0.00 ' becomes '#,##0.00_ ;-#,##0.00;')

Cheers
 
Top