Formula for Cumulative total.

[aratrika]

Kindly help me to put formula for cumulative total of a column which will continuously keep on adding while row number will increase in number.For example suppose cumulative total of column A1 to A8 now is say 100 but it may go up to A100 or may be A1000 and the formula will keep on adding. I want to put one time formula some where in the worksheet so that it will keep on adding/subtracting irrespective of increase/decrease in row number.Look forward to an early reply.


Thanks & regards

Aratrika

 

[xld]

How about a simple =SUM(A:A)

 

[Faseeh]

Hi aratrika,


Assuming that you data is present in Column A, this formula will add up new entries in that column without needing any change:

=SUM(A1:OFFSET(A1,0,0,COUNTA(A:A)))

Regards,

Faseeh

 

[bobhc]

Good day Faseeh

Daft question...but where would you put the formula? if the cell contents of the column keeps on growing do you leave A1 empty of data and put the formuls there?

 

[Faseeh]

Hi Bobhc,


I assumed the formula is not being entered in Column A, Try it out with data in Column A (A1 onward) and formula in B1.


In case i want cumulative sum in A1 and list starts from A2 downward this formula will work:

=SUM(A2:OFFSET(A2,0,0,COUNTA(A2:A10000)))

but offcourse you will have to update it after A10000.


Regards,

Faseeh

 

[bobhc]

Good day Faseeh thaks for your time and answer tried it out as per your explanation works.

 

[Montrey]

Faseeh answer is spot on. You could also put the Offset formula as a named range. Then use the name of the range instead.

 

[SirJB7]

Hi, aratrika!


Adding my two cents to what Faseeh posted, you can place this formula at bottom (next line, adjacent) of column A:


=SUMA(INDIRECTO(DIRECCION(2;COLUMNA();2;1)&":"&DIRECCION(FILA()-1;COLUMNA();4;1))) -----> in english: =SUM(INDIRECT(ADDRESS(2,COLUMN(),2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4,1)))


Regards!


EDIT:


Previous one was a general formula, for any column, just copy, paste & works. A simpler one just for an specific column (A) might be:

=SUMA(INDIRECTO("A$2:A"&FILA()-1)) -----> in english: =SUM(INDIRECT("A$2:A"&ROW()-1))