Hi! I have an excel spreadsheet where I need to sum from a column five rows at a time, how do I do this?

Question

I have a column with more than a 1,000 entries, and I need to sum the first five rows togehter, then the following five rows, etc, etc. However, I need to sum from one worksheet into another. I mean, sheet1 has the column with the values I will be summing. In sheet2 I need to populate a column that sums the values of the column in sheet1 in 5 rows increments. The first cell in sheet2 would be the sum of the first 5 rows in sheet1, the second cell in sheet2 would be the sum of the next 5 rows in sheet1, and so on. Is there a formula to do this?
Thank you a lot!
Mercedes

Answer 1

Assume A6:A1000 of Sheet1 have all data need to sum in groups of 5, in Cell A12 of Sheet2 use this formula

=SUM( OFFSET( Sheet1!A$6:A$10,( ROW(A12)-ROW( A$12))*5, 0))

Note: Sheet1!A$6:A$10 is the 1st group of 5. A12 and A$12 of above formula has to be in these formats, and has to be the cell address where the Sum list start in Sheet2, otherwise if you add/delete rows in sheet2, problem might arise.

Then copy formula from Cell A12 of sheet2 downward until you got repeated 0s, or you get 199 rows of Sum in sheet2 for the above formula cause (1000-6+1)/5=199

Please contact for more info.

Answer 2

The easiest way, for me, is a macro. The following Excel 2003 macro will sum the first 5 cells in Column A on Sheet1 and place the total in the first cell in Column B on Sheet2. The next 5 cells will be totaled and placed in cell B2, and so on. If you have 23 numbers in the first sheet, the last total will be the last 3 numbers.



If you are not using Columns A & B, change the range references in the macro before copying. Replace the "B:B' in line 9 of the macro to the Column reference for your totals in Sheet2. Also, in Line 18 change the B in 'B65536' to your Sheet2 'totals' column reference.



Replace the 'A' in Line 12 with the Column reference of your data in Sheet1. Change both references in the line.



Open your workbook.



Copy the macro to the clipboard.



Sub Sum_By_Group()

Dim rng As Range

Dim ctr, mysum

Dim rwSet

rwSet = 0

ctr = 0

Application.ScreenUpdating = False

Sheets("Sheet2").Select

Columns("B:B").Select

Selection.ClearContents

Sheets("sheet1").Select

Set rng = Range("A1:" & Range("A65536"). _

End(xlUp).Offset(2, 0).Address(0, 0))

For Each cell In rng

cell.Select

If ctr = 5 Then

Sheets("sheet2").Select

Range("B65536").End(xlUp). _

Offset (rwSet, 0).Select

ActiveCell.Value = mysum

Sheets("sheet1").Select

ctr = 1

mysum = ActiveCell.Value

rwSet = 1

Else

ctr = ctr + 1

mysum = mysum + ActiveCell.Value

End If

Next

Application.ScreenUpdating = True

End Sub



Press ALT + F11



Insert > Module



Paste the macro into the Module area to the right.



Close back to Excel



Go to Tools > Macro > Macros



Click: Options



Select a letter to be used as a keyboard shortcut.



Close back to Excel.



To run the macro, press CTRL + your shortcut letter.



Note: This macro was tested extensively and performs correctly in Excel 2003.

Answer 3

Going from one sheet to the other is no problem.

=sum(Sheet1!A1:A5) typed into cell A1 on Sheet2 will do what you want. The problem is the increment...

If you only have to do this once, you'll have a little over 200 to type in manually. You can copy&paste the formula above into 200+ rows on sheet2, then change it to A6:A10, A11:A15, etc...



You can type that in, then drag it down for 1000 rows, then delete 4 rows, skip one, delete 4, skip 1, etc...



You can use a macro and the ActiveCell.Offset command to move down 5 cells.

Answer 4

On sheet2 you will need 2 columns. I have assumed columns A and B in the example below.



Column 1 : Enter the numbers 1, 6, 11, 16, 21, etc.... (You can enter 1 in A1, 6 in B1 and then highlight both and drag down)



Column 2 : Enter the formula below in B2 and then copy down the rest of the column

=SUM(INDIRECT("Sheet1!A"&A1) : INDIRECT("Sheet1!A"&A2-1))

(no spaces in formula)

Answer 5

interior the cellular i3 enter right here formulation =sum(d3:h3) you will get the effect in i3 (i.e. sum of marks of row no.3) one i3 cellular splendid click and choose replica decide on selection i4 to i45 splendid click and choose paste you will get the effect of all rows