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

"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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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