# SUMMING

Closed
LES - Sep 8, 2009 at 08:02 PM
LES - Sep 14, 2009 at 02:22 AM
Hello,
I need a formula to sum numbers in a column. This will sound weird, sum eight rows in that column and then miss eight rows in that same column and then sum the next eight and miss the next eight so on down M1:M4500.

Thank You.

## 3 responses

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 9, 2009 at 02:58 AM
In the next column put the numbers 1 to 4500
In the next column put the formula =ROUNDUP(A1/8,0) where A1 refers to the cell containing 1
In the next column put the formula =ISODD(B1)
In a cell put the formula =SUM(A1:A30*C1:C30) Press CTRL + SHIFT + ENTER to enter the formula
here a1:A30 is your data and C1:C30 is TRUE/FALSE column
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 9, 2009 at 03:05 AM
Otherwise
enter the formula
=SUM(B1:B30*ISODD(ROUNDUP(ROW(B1:B30)/8,0))) where B1:B30 is your data range
in a cell by pressing CTRL + SHIFT+ ENTER
Hello, and thanks very much for trying to answer my problem.

I tried the formula out on a dummy set of data that I setup in column AC438:ac501 and randomly filled it with 0 and 1's. Starting with summing the 1's in the first eight cells and skipping the next eight cells etc., the dummy answer came to 21, however the formula produced 11. The wrong answer. I thought that fantastic formula deserved a better fate. I then decided to check if the formula was summing by skipping the FIRST eight and summing the next etc., and it was. Now how do I get it to start summing from the start. ? I tried changing the summing starting point by adding eight rows to the sum range but it made no difference. What do I need to change please ?

Thank you.
Les.
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 11, 2009 at 11:47 PM
In the formula i have given the first eight rows 1-8 divided by 8 and get the nearest bigger whole number will give the answer as 1, an ODD number. The formula adds all values corresponding to all such odd values. So in your case the first three rows 438-440 when divided by 8 will give odd values rest will give EVEN VALUES. So to make all the first 8 rows to give ODD values, use the formula. =SUM(Your data range here*ISODD(ROUNDUP((ROW(Your Range Here also)-5)/8,0))) change the 5 in the formula to adjust the first 8 to return ODD Values.
Brilliant Excel Guru ! Worked a charm ! Go to the TOP of the class and I'll go to the bottom.
Very very clever. Thank you.
Les.