Adding more than 30 numbers in a column

I am working on Excel 2003 and trying to add a long column of numbers. Excel
will not allow me to enter more than 30 cells to be added. They are not
continuous (cells in a single column, but every other row).

Can anyone help?

You could name, say, the first 10 as Data1, the next 10 as Data2, etc
Method is: Insert|Name->Define; Data1 refers to A1,A3,A5,A7
The use =sum(Data1+Data2+Data3....)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Fredneck Angela" <Fredneck Angela@discussions.microsoft.com> wrote in
message news:1F433108-06B1-45CA-AEB0-52F582ED558C@microsoft.com...
>I am working on Excel 2003 and trying to add a long column of numbers.
>Excel
> will not allow me to enter more than 30 cells to be added. They are not
> continuous (cells in a single column, but every other row).
>
> Can anyone help?

Did you try that? I get a #VALUE! error using the + operator.

Using the union operator works though:

=SUM(Data1,Data2,Data3)

In article <uMVav61FFHA.3244@TK2MSFTNGP15.phx.gbl>,
"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote:

> You could name, say, the first 10 as Data1, the next 10 as Data2, etc
> Method is: Insert|Name->Define; Data1 refers to A1,A3,A5,A7
> The use =sum(Data1+Data2+Data3....)

Opps, typo for comma =SUM(data1,data2...)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-E0BE0F.08195620022005@msnews.microsoft.com...
> Did you try that? I get a #VALUE! error using the + operator.
>
> Using the union operator works though:
>
> =SUM(Data1,Data2,Data3)
>
> In article <uMVav61FFHA.3244@TK2MSFTNGP15.phx.gbl>,
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote:
>
>> You could name, say, the first 10 as Data1, the next 10 as Data2, etc
>> Method is: Insert|Name->Define; Data1 refers to A1,A3,A5,A7
>> The use =sum(Data1+Data2+Data3....)

Depending on how your data is organized (if you have some consistant
identifier in each row you wish to sum, like the word "total", etc), you
might look at the SUMIF function

Vaya con Dios,
Chuck, CABGx3


"Fredneck Angela" <Fredneck Angela@discussions.microsoft.com> wrote in
message news:1F433108-06B1-45CA-AEB0-52F582ED558C@microsoft.com...
> I am working on Excel 2003 and trying to add a long column of numbers.
Excel
> will not allow me to enter more than 30 cells to be added. They are not
> continuous (cells in a single column, but every other row).
>
> Can anyone help?

IF you don't have numbers in between the ones you want to sum, just sum
the entire range.

If you do have numbers, you can use something like this:

=SUMPRODUCT(--(MOD(ROW(A1:A100),2)=0),A1:A100)

which sums the even rows. Change the 0 to 1 to sum the odd rows.

Alternatively, select your desired cells and name them by entering a
name (say, "myrange", without quotes) in the Name box at the left of the
formula bar. Then you can use

=SUM(myrange)

to sum all the cells in the named range.



In article <1F433108-06B1-45CA-AEB0-52F582ED558C@microsoft.com>,
Fredneck Angela <Fredneck Angela@discussions.microsoft.com> wrote:

> I am working on Excel 2003 and trying to add a long column of numbers. Excel
> will not allow me to enter more than 30 cells to be added. They are not
> continuous (cells in a single column, but every other row).

One way is to put a subset of the cell references in
parentheses. For example:

=SUM
((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
A89)

Notice how the first 29 cell references (A1-A57) are in
parentheses.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am working on Excel 2003 and trying to add a long
column of numbers. Excel
>will not allow me to enter more than 30 cells to be
added. They are not
>continuous (cells in a single column, but every other
row).
>
>Can anyone help?
>.
>

Thanks - this led me to the easiest answer, which was to add together two
sets of SUM(), each with fewer than 30 values inside the parenthesis.

"Jason Morin" wrote:

> One way is to put a subset of the cell references in
> parentheses. For example:
>
> =SUM
> ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> A89)
>
> Notice how the first 29 cell references (A1-A57) are in
> parentheses.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I am working on Excel 2003 and trying to add a long
> column of numbers. Excel
> >will not allow me to enter more than 30 cells to be
> added. They are not
> >continuous (cells in a single column, but every other
> row).
> >
> >Can anyone help?
> >.
> >
>

That is such a smart work-around. Personally, I doubt that I would ever need
to sum more than 30 cells in this way but it is interesting to be aware of .

Good innovative stuff!

Regards

Bob


"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:055401c5175f$4019c5d0$a501280a@phx.gbl...
> One way is to put a subset of the cell references in
> parentheses. For example:
>
> =SUM
> ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> A89)
>
> Notice how the first 29 cell references (A1-A57) are in
> parentheses.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I am working on Excel 2003 and trying to add a long
> column of numbers. Excel
> >will not allow me to enter more than 30 cells to be
> added. They are not
> >continuous (cells in a single column, but every other
> row).
> >
> >Can anyone help?
> >.
> >

BUT ... Have you tried *simply* enclosing the whole shebang in *one set* of
double quotes ?

This does work:
=SUM((A1,A3,A5,...,A200))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:055401c5175f$4019c5d0$a501280a@phx.gbl...
> One way is to put a subset of the cell references in
> parentheses. For example:
>
> =SUM
> ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> A89)
>
> Notice how the first 29 cell references (A1-A57) are in
> parentheses.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I am working on Excel 2003 and trying to add a long
> column of numbers. Excel
> >will not allow me to enter more than 30 cells to be
> added. They are not
> >continuous (cells in a single column, but every other
> row).
> >
> >Can anyone help?
> >.
> >

Just tried it with Average(), and that works also!

Wonder how many others will accept the double quotes?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:e%23wOK93FFHA.3648@TK2MSFTNGP09.phx.gbl...
> BUT ... Have you tried *simply* enclosing the whole shebang in *one set*
of
> double quotes ?
>
> This does work:
> =SUM((A1,A3,A5,...,A200))
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
> news:055401c5175f$4019c5d0$a501280a@phx.gbl...
> > One way is to put a subset of the cell references in
> > parentheses. For example:
> >
> > =SUM
> > ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> > 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> > 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> > A89)
> >
> > Notice how the first 29 cell references (A1-A57) are in
> > parentheses.
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >I am working on Excel 2003 and trying to add a long
> > column of numbers. Excel
> > >will not allow me to enter more than 30 cells to be
> > added. They are not
> > >continuous (cells in a single column, but every other
> > row).
> > >
> > >Can anyone help?
> > >.
> > >
>

RD,

Are you getting your quotes confused with your parentheses? Maybe time to
fix another drink <g>

Bob


"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:e%23wOK93FFHA.3648@TK2MSFTNGP09.phx.gbl...
> BUT ... Have you tried *simply* enclosing the whole shebang in *one set*
of
> double quotes ?
>
> This does work:
> =SUM((A1,A3,A5,...,A200))
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
> news:055401c5175f$4019c5d0$a501280a@phx.gbl...
> > One way is to put a subset of the cell references in
> > parentheses. For example:
> >
> > =SUM
> > ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> > 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> > 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> > A89)
> >
> > Notice how the first 29 cell references (A1-A57) are in
> > parentheses.
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >I am working on Excel 2003 and trying to add a long
> > column of numbers. Excel
> > >will not allow me to enter more than 30 cells to be
> > added. They are not
> > >continuous (cells in a single column, but every other
> > row).
> > >
> > >Can anyone help?
> > >.
> > >
>

Yes, I DID mean Parenthesis !

And you're right, since it's 3 minutes after 12, time to start on today's
ration of Grog.<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eH6CLZ4FFHA.3972@TK2MSFTNGP15.phx.gbl...
> RD,
>
> Are you getting your quotes confused with your parentheses? Maybe time to
> fix another drink <g>
>
> Bob
>
>
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> news:e%23wOK93FFHA.3648@TK2MSFTNGP09.phx.gbl...
> > BUT ... Have you tried *simply* enclosing the whole shebang in *one set*
> of
> > double quotes ?
> >
> > This does work:
> > =SUM((A1,A3,A5,...,A200))
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
> -
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
> -
> > "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
> > news:055401c5175f$4019c5d0$a501280a@phx.gbl...
> > > One way is to put a subset of the cell references in
> > > parentheses. For example:
> > >
> > > =SUM
> > > ((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,A23,A25,A27,A29,A
> > > 31,A33,A35,A37,A39,A41,A43,A45,A47,A49,A51,A53,A55,A57),A5
> > > 9,A61,A63,A65,A67,A69,A71,A73,A75,A77,A79,A81,A83,A85,A87,
> > > A89)
> > >
> > > Notice how the first 29 cell references (A1-A57) are in
> > > parentheses.
> > >
> > > HTH
> > > Jason
> > > Atlanta, GA
> > >
> > > >-----Original Message-----
> > > >I am working on Excel 2003 and trying to add a long
> > > column of numbers. Excel
> > > >will not allow me to enter more than 30 cells to be
> > > added. They are not
> > > >continuous (cells in a single column, but every other
> > > row).
> > > >
> > > >Can anyone help?
> > > >.
> > > >
> >
>
>

I'm a bit confused about the OP not being allow to sum 30 cells. In excel97, I pasted in a number 22 in alternate cells begining in A1 to A103, and had no problem with =SUM(A1:A103) which totalled 1144. Could someone kindly explain, please.