Adding more than 30 numbers in a column

[CLR]

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

 

[Guest]

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?

 

[Bernard Liengme]

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

 

[JE McGimpsey]

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.

 

[Jason Morin]

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

 

[JE McGimpsey]

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

Using the union operator works though:

=SUM(Data1,Data2,Data3)

 

[Guest]

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.

 

[Bernard Liengme]

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

 

[Bob Phillips]

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

 

[Ragdyer]

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

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

 

[Ragdyer]

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

Wonder how many others will accept the double quotes?

 

[Bob Phillips]

RD,

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

Bob

 

[Ragdyer]

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>