Repeat the same operation at another (over and over again)

[ingemars]

I have over 10,000 rows and 3 columns. And I want totals at certain intervals (say after every 110th row), then a “horizontal” total of the columns at that stop. These results could be placed in new columns beside, “pivot style”.
This operation must be repeated throughout all the 10,000 records (rows), hence about 90 times.
The interval (say 110 rows) will be the same.
Is there any formula without going VBA?

I attach a small sample to describe the idea. Yellow is where the formulas are made, blue is where it should be repeated (here only after 8 rows for simplicity)
Thanks for ideas,
Ingemar
ingemar@saevfors.se

[Pete_UK]

I'm not sure why your total in column F is adding columns C to E - doesn't that mean that you are double-counting?

I presume your data is continuous, so it might be better to have a separate table of totals, so you can put this formula in G4:

=SUM(INDEX(C:C,(ROWS($1:1)-1)*9+4):INDEX(C:C,ROWS($1:1)*9+3))

Copy this into H4:I4 and put your SUM formula in J4. Then you can copy this down as far as you need to.

Note that in your example you are summing over 9 rows (hence the 9 in red in the formula - you will probably want to make this 110 in your real file). Also, your data starts on row 4 (hence the 4 in blue in the formula, and the 3 in purple - this should be 1 less than the start row). Also, you may need to use a semicolon ( ; ) instead of the commas ( , ) in the formula.

Hope this helps.

Pete

[ingemars]

Pete,
Thanks a million for suggesting a solution to this number crunching.
I tried your formula on the data but there were some differences, see colored areas.
I’m not that comfortable with the INDEX syntax and need to study it more. Did you use the Array form? Is this use of brackets not that important?
Thanks for helping,
Ingemar

[Pete_UK]

You forgot to adjust the 4 and the 3, as your data starts in row 3 - you can change the formula in L2 to this:

=SUM(INDEX(C:C,(ROWS($1:1)-1)*110+3):INDEX(C:C,ROWS($1:1)*110+2))

then copy across and down.

If you are not happy using INDEX (which returns a cell reference in the above formula), then you can use this alternative (e.g. in cell O2):

=SUMIFS(C:C,$A:$A,RIGHT($K3,4))

(use semicolons instead of commas). You can copy this into P2:Q2, then copy the formulae down - it relies on you having the word "year " in front of the actual year in cell K3 and below.

Hope this helps.

Pete

[ingemars]

Thanks Pete,
It works perfectly now.
Can you recommend any EXCEL FORMULA literature to get more insight in the syntax writing?
Ingemar

[hemesh]

wrong post