Cumulative Total with Blank Cells

nickvaf

New Member
Joined
Nov 2, 2014
Messages
5
This I didn't think would be hard, but is causing me quite the headache. I have data in one column (Net Amount) that has double spaces after every few lines. In another column, I want to keep a cumulative total, but not include those spaces in the cumulative total column (I have been able to run cumulative total, but is places the same number 3 times in a row because of the empty cells in the first column). Does anyone have a function that can achieve what I mirrored below (instead of manually adding it like I did)? Thanks!

Net AmountCumulative Total
80008000
400012000
200016000
700023000
26000
35000
300045000
900045200
1000047200
20052200
200053092
500053200
53275
53475
892
108
75
200

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi nickvaf,

Welcome to MrExcel!!

Not sure why but your cumulative formula is wrong from Row 3 :confused:

That said, try this where it's assumed the numbers start in A2:

=IF(LEN(A2)=0,B1,A2+IF(ISNUMBER(B1)=FALSE,0,B1))

HTH

Robert
 
Upvote 0
Hi,

Thank you for the quick reply! And you're right the reason it is wrong is because I was quickly doing it in my head. The third number in the 1st row should read 4000.

The formula I feel is close, but I tried it out and it still behaves a little strangely and complains of referring to empty cells. The third column is how your formula appears in excel. Above the row with the 8000s is an empty row.

80008000 8000
40001200012000
40001600016000
70002300023000
2600023000
3500026000
30004500038000
90004520054000
100004720055200
2005220047400
20005309254200
50005320058092
5327553200
5347553275
89254367
108108
7575
200200
0
0
0

<tbody>
</tbody>
 
Upvote 0
B2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($B$2:B2)<=COUNT($A$2:$A$19),
  MIN(IF(SUBTOTAL(9,OFFSET($A$2,0,0,ROW($A$2:$A$19)-ROW($A$2)+1))>N(B1),
  SUBTOTAL(9,OFFSET($A$2,0,0,ROW($A$2:$A$19)-ROW($A$2)+1)))),"")
 
Upvote 0
The third column is how your formula appears in excel.

They're not the results I get :confused:

Aladin is a legend with Excel formulas so I'm sure his will do the trick, but assuming your data actually starts at Row 3 this should do the job too:

=IF(ROW()=3,A3,SUM($A$3:A3))

Regards,

Robert
 
Upvote 0
Net AmountCumulative Total
80008000
400012000
200014000
700021000
24000
33000
300043000
900043200
1000045200
20050200
200051092
500051200
51275
51475
892
108
75
200

<COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3726" width=105><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5432" width=153><TBODY>
</TBODY>

The data is in A2:A19, with A2 = 8000, the first data point. Second column obtains when you invoke the SUBTOTAL formula I already posted.
 
Upvote 0
Thank you so much! Only thing is I had given you slightly different data than what I am working with for the sake of simplicity. When I attempted it on my excel document, the equation mostly works but for some reason it switches the order of a few of the cumulative sums. Attached is the original data if you'd like a go. Thank you for your help though! EDIT: Just noticed it had to do with ordering numbers from least to greatest. I know this is a really weird equation so if it's too much of a headache I understand.

NetRunning total (manual)Running total (Equation)
770277027702
101741787617876
99002777624201
-11992657726577
35473012427776
2420130124
4860148601
-59235657156571
244007934779347
79708454784547
227769780797807
5200117552117552
131462124887
124887131462
13260137642137642
19745
13910
-6575
12755
16155
31100
11590
22615
-1183
-1170
370
20105
22370
12711
75045
-5000
6945
12564
-22199
5409
23390
-4675
7961

<tbody>
</tbody>
 
Last edited:
Upvote 0
I am referring to your latest formula.

B2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($B$2:B2)<=COUNT($A$2:$A$46),SUM($B1,INDEX($A$2:$A$46,
  INDEX(SMALL(IF(ISNUMBER($A$2:$A$46),ROW($A$2:$A$46)-ROW($A$2)+1),
  ROW(INDIRECT("1:"&COUNT($A$2:$A$46)))),ROWS($B$2:B2)))),"")
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top