Running total

[ZerglingPack]

I've been tasked with creating a spread sheet that will show a running total, but I'm not sure where to start.

Amount	Total
1
2
3
x
	6
1
2
6
4
5
x
	18

<tbody>
</tbody>

It started out simple enough, enter amounts on the left and a cell on the right would show the total. My problem is that the boss wants to be able to put an X (or any simple way) and it would total the numbers after the previous X. The big issue is that the "amount" column doesn't have a specific amount of cells before needing a total. It could be as few as two amounts or up to fifty before the next X. Any suggestions or solutions would be greatly appreciated.

 

[steve the fish]

Try this in B2:

=IF(A2="x",SUM($A$1:A1)-SUM($B$1:B1),"")

 

[ZerglingPack]

Try this in B2:

=IF(A2="x",SUM($A$1:A1)-SUM($B$1:B1),"")

This is almost exactly what I am looking for, but how do I make it calculate more than just A1?

 

[ZerglingPack]

NVM I'm blind. Thank you very much Steve! Do you know how I could make it so the totals are directly under each other instead of spaced out?

 

[steve the fish]

I cant think of a way to do that. There may be one but not one that immediately springs to mind using formulas.

 

[steve the fish]

Actually this array formula should work. Enter it CTRL-SHIFT-ENTER not just ENTER. In B2 and copy down:

=IFERROR(SUM($A$1:INDEX($A$1:$A$1000,SMALL(IF($A$2:$A$1000="x",ROW($A$2:$A$1000)),ROWS($A$1:A1))))-SUM($B$1:B1),"")

 

[ZerglingPack]

Actually this array formula should work. Enter it CTRL-SHIFT-ENTER not just ENTER. In B2 and copy down:

=IFERROR(SUM($A$1:INDEX($A$1:$A$1000,SMALL(IF($A$2:$A$1000="x",ROW($A$2:$A$1000)),ROWS($A$1:A1))))-SUM($B$1:B1),"")

This is exactly what I am looking for, you are amazing! I'm not even sure what the formula means, but it's doing exactly what my boss wants. Thank you for everything.

 

[mse330]

Would this non-array formula achieve your target? Assuming your numbers in column A & the totals should appear in column B

=IF(A2="x",SUM($A$1:A2)-SUM($B$1:B1),"")

 

[ZerglingPack]

MSE330 that was close to what I was looking for, but the Array that Steve posted was nearly exactly what I thought the boss wanted. Is it possible to have the numbers between the X added to the next set? Basically he wanted the X as a quick way of moving the information to another cell, but the amounts between each x should be added to the previous set of numbers. Is that possible?

 

[ZerglingPack]

I was able to figure it out, I just removed -sum, but now my question is how would I add multiple columns for the numbers I input? The total would still be in one column, but lets say I wanted to input numbers in column A and B with the total moving to C.