sum formula function!

[asyamonique]

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=282 border=0><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ddd9c3" width=94 height=20>A1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>B1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>C1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>200</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-200</TD></TR></TBODY></TABLE>

Good Day,
C1 cell has a formula

=sum(b1-a1)

Just wondering that how can i avoid the C1 value if there is no value yet on B1 cell like given sample above? And also is it possible to make invisible the zero text on C1 if A1 and B1 has no value?
Many Thanks

 

[cstimart]

Try this...

=IF(OR(A1=0,B1=0),"",SUM(A1-B1))

 

[asyamonique]

Try this...

=IF(OR(A1=0,B1=0),"",SUM(A1-B1))

Thanks Cstimart,

 

[T. Valko]

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=282 border=0><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ddd9c3" width=94 height=20>A1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>B1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>C1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>200</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-200</TD></TR></TBODY></TABLE>

Good Day,
C1 cell has a formula

=sum(b1-a1)

Just wondering that how can i avoid the C1 value if there is no value yet on B1 cell like given sample above? And also is it possible to make invisible the zero text on C1 if A1 and B1 has no value?
Many Thanks

Here's one way...

=IF(COUNT(A1,B1)<2,"",B1-A1)

 

[asyamonique]

Here's one way...

=IF(COUNT(A1,B1)<2,"",B1-A1)

Valko Thanks for it.
May i ask u that how can we invisible the zero value on the formula below?

=SUM(C2:C13)

Thanks again.

 

[Aladin Akyurek]

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=282 border=0><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ddd9c3" width=94 height=20>A1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>B1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ddd9c3" width=94>C1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>200</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-200</TD></TR></TBODY></TABLE>

Good Day,
C1 cell has a formula

=sum(b1-a1)

Just wondering that how can i avoid the C1 value if there is no value yet on B1 cell like given sample above? And also is it possible to make invisible the zero text on C1 if A1 and B1 has no value?
Many Thanks

Maybe something like this...

=IF(COUNT(A1:B1)=2,IF(A1-B1,A1-B1,""),"")

 

[T. Valko]

Valko Thanks for it.
May i ask u that how can we invisible the zero value on the formula below?

=SUM(C2:C13)

Thanks again.

Like this...

=IF(SUM(C2:C13),SUM(C2:C13),"")

 

[T. Valko]

Valko Thanks for it.
May i ask u that how can we invisible the zero value on the formula below?

=SUM(C2:C13)

Thanks again.

Like this...

=IF(SUM(C2:C13),SUM(C2:C13),"")

Another way is to format the cell to suppress the display of 0 values.

Select the formula cell
Right click>Format Cells
On the Number tab select Custom
In the little box under Type enter this format code:

General;-General;

OK out

With this method the cell will still contain numeric 0 you just won't see it.

 

[asyamonique]

Another way is to format the cell to suppress the display of 0 values.

Select the formula cell
Right click>Format Cells
On the Number tab select Custom
In the little box under Type enter this format code:

General;-General;

OK out

With this method the cell will still contain numeric 0 you just won't see it.

Many thanks.

 

[T. Valko]

Many thanks.

You're welcome!