ExcelBanter - Summing non consecutive cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Summing non consecutive cells (https://www.excelbanter.com/excel-discussion-misc-queries/3385-summing-non-consecutive-cells.html)

csfrolich

Summing non consecutive cells

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Gary Rowe

Use two helper columns (column G and H for instance) and put the formula
=IF(MOD(CELL("row",F5),2)=0,F5,0) in Column G for even rows and use formula
=IF(MOD(CELL("row",F5),1)=0,F5,0) in Column H for odd rows.
Gary

"csfrolich" wrote:

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

CLR

I would use a helper column and put "odd" or "even" respectively in each
row..........this can be copied down very quickly..........

Then use something like =SUMIF(B:B,"odd",A:A)

Vaya con Dios,
Chuck, CABGx3

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Gary Rowe

oops! My second formula should read =IF(MOD(CELL("row",F5),2)=1,F5,0). Sorry.

"Gary Rowe" wrote:

Use two helper columns (column G and H for instance) and put the formula
=IF(MOD(CELL("row",F5),2)=0,F5,0) in Column G for even rows and use formula
=IF(MOD(CELL("row",F5),1)=0,F5,0) in Column H for odd rows.
Gary

"csfrolich" wrote:

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Ragdyer

For odd rows, starting at F5 down to F957, try this:

=SUMPRODUCT((MOD(ROW(F5:F957)-5,2)=0)*(F5:F957))

For even rows, starting at F6 down to F958, try this:

=SUMPRODUCT((MOD(ROW(F6:F958)-6,2)=0)*(F6:F958))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

csfrolich

Gary,

Thanks, after your correction theu both worked.
Any insight on the MOD CELL commands or direction to aid in my understanding
would be greatly appreciated.

Thanks again

cs frolich

"Gary Rowe" wrote:

oops! My second formula should read =IF(MOD(CELL("row",F5),2)=1,F5,0). Sorry.

"Gary Rowe" wrote:

Use two helper columns (column G and H for instance) and put the formula
=IF(MOD(CELL("row",F5),2)=0,F5,0) in Column G for even rows and use formula
=IF(MOD(CELL("row",F5),1)=0,F5,0) in Column H for odd rows.
Gary

"csfrolich" wrote:

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

csfrolich

CLR,

I tried your formula, and quickly learn that the B:B function designated
column B.
I did try to change the B's ... to F's .
Since I was working in column F. I do not understand the A:A. I do
understand changing the odd to even. My array is F6 down to F922. If your
formula could relate to this array, I may get it a bit more.

Thanks

cs frolich

"CLR" wrote:

I would use a helper column and put "odd" or "even" respectively in each
row..........this can be copied down very quickly..........

Then use something like =SUMIF(B:B,"odd",A:A)

Vaya con Dios,
Chuck, CABGx3

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

csfrolich

BINGO,

Awesome formula. Nailed the answer in one cell.
Any help in me understand this would be greatly appreciated.

Thanks

cs frolich

"Ragdyer" wrote:

For odd rows, starting at F5 down to F957, try this:

=SUMPRODUCT((MOD(ROW(F5:F957)-5,2)=0)*(F5:F957))

For even rows, starting at F6 down to F958, try this:

=SUMPRODUCT((MOD(ROW(F6:F958)-6,2)=0)*(F6:F958))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Aladin Akyurek

1]

=SUMPRODUCT(--(MOD(ROW($F$5:$F$958)-CELL("Row",$F$5)+0,2)=0),$F$5:$F$958)

2]

=SUMPRODUCT(--(MOD(ROW($F$5:$F$958)-CELL("Row",$F$5)+1,2)=0),$F$5:$F$958)

csfrolich wrote:
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Aladin Akyurek

Bob Phillips

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

"csfrolich" wrote in message
...
BINGO,

Awesome formula. Nailed the answer in one cell.
Any help in me understand this would be greatly appreciated.

Thanks

cs frolich

"Ragdyer" wrote:

For odd rows, starting at F5 down to F957, try this:

=SUMPRODUCT((MOD(ROW(F5:F957)-5,2)=0)*(F5:F957))

For even rows, starting at F6 down to F958, try this:

=SUMPRODUCT((MOD(ROW(F6:F958)-6,2)=0)*(F6:F958))
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add
all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

CLR

It's kind of academic now since you seem happy with the SUMPRODUCT solution,
but just to close the loop.......

Assume column G as your helper column in which you put the "odd" and "even"
notations.........then the formula could be modified as follows to fit your
range:

=SUMIF(G6:G922,"odd",F6:F922)

Vaya con Dios,
Chuck, CABGx3

"csfrolich" wrote in message
...
CLR,

I tried your formula, and quickly learn that the B:B function designated
column B.
I did try to change the B's ... to F's .
Since I was working in column F. I do not understand the A:A. I do
understand changing the odd to even. My array is F6 down to F922. If your
formula could relate to this array, I may get it a bit more.

Thanks

cs frolich

"CLR" wrote:

I would use a helper column and put "odd" or "even" respectively in each
row..........this can be copied down very quickly..........

Then use something like =SUMIF(B:B,"odd",A:A)

Vaya con Dios,
Chuck, CABGx3

"csfrolich" wrote in message
...
I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add
all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

Gary Rowe

Cell with "row" as the information type returns the row number of the cell
the data is in. Mod (as well as the Cell function) are explained in the Help
file. Simply click on the fx on the formula bar and type in the function and
click on the "help on this function" and a detailed explanation will be
provided. Or simply search on the function name.
Gary

"csfrolich" wrote:

Gary,

Thanks, after your correction theu both worked.
Any insight on the MOD CELL commands or direction to aid in my understanding
would be greatly appreciated.

Thanks again

cs frolich

"Gary Rowe" wrote:

oops! My second formula should read =IF(MOD(CELL("row",F5),2)=1,F5,0). Sorry.

"Gary Rowe" wrote:

Use two helper columns (column G and H for instance) and put the formula
=IF(MOD(CELL("row",F5),2)=0,F5,0) in Column G for even rows and use formula
=IF(MOD(CELL("row",F5),1)=0,F5,0) in Column H for odd rows.
Gary

"csfrolich" wrote:

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.

Thanks

All times are GMT +1. The time now is 09:05 PM.