Running totals using an array of odd cells

[TICKERTYBOO]

Hi Guys,

I am having difficulty being able to set up a simmple formula to create a 'running total' cell, which adds up an array of seperate cells & more importantly when I change to figures/numbers in the sperate cells, they keep on adding up in the running total?

I am using cells: N4 N7 N10 N13 N16 N19 N22 N25 N28 N31 N34 N37 N40 N43 N47 N50

The running total is in cell 'N59'

So say they is the number 10 in each of the long list of cells above (N4 ~ N50) 16 cells x 10 = 160 in the running total.

Then say next month I need to change to amounts in the cells etc: N4 (5) N7 (30) N10 (41) N13(12) N16(27) so the new 'running total' should be (114 + 160) = 274

P.S I cannot use the 'empty cells' in the same column as these have different data in!!

What is an easy formula for this as I have spent way to long trying to figure it out to no avail................please help??

 

[XOR LX]

Hi,

Not sure how you want to progress after this, but:

=SUMPRODUCT((MOD(ROW(N4:N55)-MIN(ROW(N4:N55)),3)=0)*N4:N55)

will give you the same result as:

=SUM(N4,N7,N10,N13,N16,N19,N22,N25,N28,N31,N34,N37,N40,N43,N46,N49,N52,N55)

Regards

 

[TICKERTYBOO]

Hi OldBrewer,

Thank you very much for your speedy reply...............there is just a slight hitch. I havern't a clue how to do a 'macro'.

I would really appreciate it if you could make your 'formula' 'Numpty Proof'!!!LOL

However plain speaking you make it I certainly Will Not be embarrased or affended; quite the opposite!

I'm on windows 7 excel '13 if that helps?
Many Thanks again

 

[oldbrewer]

assume your first sheet is sheet1 and you make a new sheet named runtot

in runtot B1=A1
B2=A2+B1
B3= A3+B2 etc etc

now this is the macro for sheet runtot

cells(1,3)=cells(1,3)+1
cells(cells(1,3),1)=sheet1!n59
sheet1!n59=cells(cells(1,3),2
Endsub

each month you run it it will bring the monthly total over, calculate the new running total and put that value in N59