How do you sum a range of more than 30 cells. I get error each time I try.

[Joseph Conaghan via OfficeKB.com]

Here is the error message:

You've entered too many arguments for this function.

To get help with entering arguments for the function, click OK to close
this message. Then, on the formula bar, click the equal sign button
(located to the left of the equal sign in your formula).

 

[Fredrik Wahlgren]

Here is the error message:

You've entered too many arguments for this function.

To get help with entering arguments for the function, click OK to close
this message. Then, on the formula bar, click the equal sign button
(located to the left of the equal sign in your formula).

Instead of entering something like =SUM(A1,A2,A3), use =SUM(A1:A32)

7fredrik

 

[Bob Umlas]

Use a 2nd (or 3rd...) set of parentheses:
=SUM(A1,A5,(A7,A18,A29),A52), for example will have Excel "think" there's 4
items being added instead of 6. Use them freely, and you can get practically
any number of items!

Bob Umlas
Excel MVP

 

[Joseph Conaghan via OfficeKB.com]

Good idea. But the cells are not sequential. They are in various
locations on the spreadsheet.

 

[Jason Morin]

The first step is to consolidate the ranges as Fredrik
suggested. If, after this step, you still have more than
30 ranges, consolidate further using defined names, or
try this technique:

You can use another set of parentheses within AVERAGE to
get around the limit of 30 arguments. For example:

=AVERAGE
((A2,A4,A6,A8,A10,A12,A14,A16,­
A18,A20,A22,A24,A26,A28,A30,
A32,A34,A36,A38,A41,A43,A45,A4­
7,A49,A51,A53,A55,A57,A59),A
61,A63,A65,A67,A69,A71,A73,A75­,A77,A80,A82,A84)


Notice how the A2 to A59 are enclosed in ( ).


HTH
Jason
Atlanta, GA

 

[Joseph Conaghan via OfficeKB.com]

Thanks Bob. The user will try this tomorrow and I'll post back to update
all.