Cumulative totals in Excel

[mondeoman]

In cell (say A40) I have a calculated figure of Fee plus VAT. This has to be taken away from an income figure but accululates each month. So for example the figure in January is say £126. What I want is in February this to be the January figure plus the same again in February and then in March the combine February figure plus the samer again for March.

So in effect it would be for February = A40+B40 and in March =B40+C40, April would be =C40+D40 and so on. Is there a better way of doing this please.

 

[SkipVought]

hi,

Not knowing the structure of your sheet, let me state and assumption and a subsequent solution.

Months as REAL DATES in row 1, Jan starting in B1.
[tt]
A40: =SUM(OFFSET(B40,0,0,1,COUNTIF(B1:M1,"<="&TODAY())))
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MCuthill]

mondeoman,

If this calcuated value "Fee plus VAT" exists once for each month, and structured something like:

[tt]Row#... Column A ... Column B ... Column C
001 ... Month ...... Fee + VAT .. Running Total
002 ... January .... $10 ........ $10
003 ... February ... $20 ........ $30
004 ... March ...... $10 ........ $40[/tt]

Column C should have a formula like:
=SUM(Top of Range (Locked) : Current Row)
Formula for C2 (January): =SUM($C$2:C2)
Formula for C3 (February): =SUM($C$2:C3)
this can be filled down your entire range.

Your approach will also work, and can be filled down if your spreadsheet is structured somewhat like the above.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

mondeoman,

my apologies... I reread your post and see it is horizontally, not vertically you wish to calculate this.

Please transpose my solution.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

[tt]
C2: =SUM($B$2:B2)
[/tt]
copy down.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mondeoman]

Thank you all for your contributions. I have now used the solution =$A$40+A40)which seems to work. I am still tryng to develop my spreadsheet so no doubt will have other questions in due course. Thank you for your hel and interest.

 

[SkipVought]

[tt]
=$A$40+A40
[/tt]
will NOT SUM from A40 to wherever. Your FORMULA is in A40!!! It should not reference itself.

This formula ADDs A40 and A40.
[b\
Try the formula I initially posted.[/b] I got comfused by the other member's post, thinking it was your response.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]