Sum or leave blank

[Charles_]

Hi all,

I am trying to work out a formula that will do a calculation OR don't and leave blank if there is a zero or a blank cell.

Thanks in advance.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C (expected result of formula)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]=A1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3+A4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]IF in A2 there is a number that it is not '0' or blank, leave this cell blank. Otherwise =A1+A2+A3+A4+A5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Charles_]

Apologies I am not sure why my tables don't show properly!

 

[Fluff]

Is this what you want

Excel 2013/2016
	A	B	C
1	10		10
2	18		28
3	12		40
4	0
5
6	11		51
Anvil Cottage 001

Cell Formulas
Range	Formula
C1	=A1
C2	=IF(OR(A2={"",0}),"",SUM(A$1:A2))

 

[dfsobral]

or in C1 and copy down

=IF(A1<>0, "", sum($A$1:A1))

Probably faster
it will not sum if you add text

 

[Charles_]

Thank you once again.

I am using the table to feed into a line graph and it picks up the empty cell as '0' rather than nothing. If the cell is left blank, the graph shows it correctly (gap) but with the formula it brings it down to '0'. Is there something that can be added to the formula?

 

[Aladin Akyurek]

What if we would have the following?

A1 = 10
A2 = 18
A3 = 0
A4 = 8
A5 empty

 

[dfsobral]

if you place a formula even if shows nothing there is something there

that's why the graph returns 0

in your case i can only think in a macro

if you are you ok with it?

 

[dfsobral]

if you are ok with a macro try this

Sub FillFormula
Dim i as Long, x as Long

i=Cells(Rows.Count, 1).End(xlUp).Row

For x=1 to i
     If Not Cells(x,1)=0 Then Cells(x,3).FormulaR1C1= "=SUM(R1C1:RC[-2])"
Next x
End Sub

 

[Charles_]

if you place a formula even if shows nothing there is something there

that's why the graph returns 0

in your case i can only think in a macro

if you are you ok with it?

Thank you but I can't use a macro in this case, I wonder if I can point the graph to a different range and have a formula there to paste values only, is that possible?

 

[Aladin Akyurek]

Thank you but I can't use a macro in this case, I wonder if I can point the graph to a different range and have a formula there to paste values only, is that possible?

Care to reply to the post #6 ?