Cumulative total relating to daily updated data

[alancyoung]

Please help! I am trying to get a formula to keep a running cumulative total at the end of a row, but only if the row below is populated. See example below


A B C D E F
Row1 100 0 200 100 100 =300
Row2 100 200 100 =400

D2, E2 and F2 do not have data yet, so don't want to SUM E1 and F1 until populated.

I've tried SUMIFS formula, using >0, but when E2 is a 0 value due to formula, it won't SUM, and there are times when it is a zero value, I do want it to SUM!!!!

Please Help!!!!!

 

[Robert Mika]

D2, E2 and F2 do not have data yet, so don't want to SUM E1 and F1 until populated.

What about D1?
Your layout is out of sync.
How did yoiu get 400 in second row?

 

[Oeldere]

Why is the answer in row 1 300?

I expect it to be 500.

 

[alancyoung]

Why is the answer in row 1 300?

I expect it to be 500.

Hi. Sorry, I missed D1 out.

Row 1 is 2011 sales by month.
Row 2 is 2012 sales by month.

I'm looking to compare Year to date 2012 to same period in 2011
whilst still showing each monthly total for entire year 2011.

2012 is updated monthly.

Hope you can help.

Thanks

 

[alancyoung]

[TABLE="width: 896"]
<tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Jan[/TD]
[TD="class: xl65, width: 64"]Feb[/TD]
[TD="class: xl65, width: 64"]Mar[/TD]
[TD="class: xl65, width: 64"]Apr[/TD]
[TD="class: xl65, width: 64"]May[/TD]
[TD="class: xl65, width: 64"]Jun[/TD]
[TD="class: xl65, width: 64"]Jul[/TD]
[TD="class: xl65, width: 64"]Aug[/TD]
[TD="class: xl65, width: 64"]Sep[/TD]
[TD="class: xl65, width: 64"]Oct[/TD]
[TD="class: xl65, width: 64"]Nov[/TD]
[TD="class: xl65, width: 64"]Dec[/TD]
[TD="class: xl65, width: 64"]YTD[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2011[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]700[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2012[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]800[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully this makes it a little clearer. 800 in YTD column is just SUM of Row 2012.
700 in YTD Column of 2011 Row is the problem, particularly when a zero value appears in Apr 2012.

So in July, if i input 100 in 2012 row, I want YTD value in 2012 row to read 900, and YTD value in 2011 row to read 700, due to the 0 in July 2011.

Hope this is slightly clearer.

 

[Robert Mika]

like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
2	2011	100	100	0	100	200	200	0	100	100	200	100	100	700
3	2012	200	200	100	0	100	200	100						900
Sheet1

Cell Formulas
Range	Formula
H2	=H6
N2	=SUMIFS($B$2:$M$2,$B$3:$M$3,"<>")
N3	=SUM(B3:M3)

 

[alancyoung]

Thank You! I knew it would be something simple (To you, not me!)

Much appreciated

 

[alancyoung]

Sorry to be a pain, but still got a slight issue with formula.

If cell I2:M2 contains a formula linking to another sheet, which at present delivers a 0 value, then N2 returns 1300, but I only want it to return 700 like above.

Hope you can help further still.

Thanks

 

[Robert Mika]

Look below there are mixture of harcoded and formula values and I'm getting result as below:
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]700[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]900[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(H6=0,"",H6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(I6=0,"",I6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=IF(J6=0,"",J6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=K6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=L6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=M6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=SUMIFS($B$2:$M$2,$B$3:$M$3,"<>")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=H7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=I7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=J7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K3[/TH]
[TD="align: left"]=K7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=L7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M3[/TH]
[TD="align: left"]=M7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N3[/TH]
[TD="align: left"]=SUM(B3:M3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[alancyoung]

Thanks again, but still an issue. See below:

Cell H3 left Blank:

[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Jan[/TD]
[TD="class: xl65, width: 64"]Feb[/TD]
[TD="class: xl65, width: 64"]Mar[/TD]
[TD="class: xl65, width: 64"]Apr[/TD]
[TD="class: xl65, width: 64"]May[/TD]
[TD="class: xl65, width: 64"]Jun[/TD]
[TD="class: xl65, width: 64"]Jul[/TD]
[TD="class: xl65, width: 64"]Aug[/TD]
[TD="class: xl65, width: 64"]Sep[/TD]
[TD="class: xl65, width: 64"]Oct[/TD]
[TD="class: xl65, width: 64"]Nov[/TD]
[TD="class: xl65, width: 64"]Dec[/TD]
[TD="class: xl65, width: 64"]YTD[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2011[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl66, align: right"]700[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2012[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]700[/TD]
[/TR]
</tbody>[/TABLE]

Cell H3 contains formula IF(H7=0," ",H7):

[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Jan[/TD]
[TD="class: xl65, width: 64"]Feb[/TD]
[TD="class: xl65, width: 64"]Mar[/TD]
[TD="class: xl65, width: 64"]Apr[/TD]
[TD="class: xl65, width: 64"]May[/TD]
[TD="class: xl65, width: 64"]Jun[/TD]
[TD="class: xl65, width: 64"]Jul[/TD]
[TD="class: xl65, width: 64"]Aug[/TD]
[TD="class: xl65, width: 64"]Sep[/TD]
[TD="class: xl65, width: 64"]Oct[/TD]
[TD="class: xl65, width: 64"]Nov[/TD]
[TD="class: xl65, width: 64"]Dec[/TD]
[TD="class: xl65, width: 64"]YTD[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2011[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl66, align: right"]800[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2012[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]700
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, with the formula in, it is summing the Jul 2011 figure, but when left blank, it doesn't.

Any further suggestions?

Alan