# SUMMING

Closed## 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

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

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

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

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

Thank you.

Les.

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

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.

- Connect easily with those who share the same interests
- Easily follow your discussions and receive more replies
- Showcase your expertise by helping other members
- Take advantage of many additional features by registering