Summing non consecutive cells

[csfrolich]

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

 

[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

 

[Gary Rowe]

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

 

[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))

 

[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

 

[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

 

[csfrolich]

BINGO,

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

Thanks

cs frolich

 

[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)

 

[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)

 

[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)

 

[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

 

[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