Too many arguements

[Guest]

In my worksheet I have a SUM equation which sums every subtotal. I currently
have 30 subtotals.
When I try to add one more I get a the following message:

You have entered too many arguements for this function. It then directs me
to the = sign on the formula bar.

How do I update this without combining some of the earlier subtotals

Thanks - PJ

 

[SteveG]

PJ,

Are your subtotals in a continuous range i.e. A3:AZ3? If so sum th
range,

=SUM(A3:AZ3).

The SUM function is limited to 30 arguments so if you are doing eac
separately like =SUM(A3,B3,C3,D3......) then it will give you tha
error message when you try to exceed 30. If say some of your subtotal
are in ranges then you can sum up to 30 ranges as arguments as well s
if your subtotals are in cells A3:C3, D5:E5, F7:H7 and so on then

=SUM(A3:C3,D5:E5,F7:H7........)

HTH


Cheers,

Stev

 

[Guest]

Try this:
Replace your SUM functions with SUBTOTAL functions. SUBTOTAL offers many
options sum, min, max...etc. (see Excel Help) One of it's better features is
that is ignores other SUBTOTAL functions!

For your application (sum) you would use this variation:
A11: =SUBTOTAL(9,A1:A10)
A21: =SUBTOTAL(9,A12:A20)

and for the grand total:
A21: =SUBTOTAL(9,A1:A20)


Does that help?

***********
Regards,
Ron

 

[Ron Rosenfeld]

In my worksheet I have a SUM equation which sums every subtotal. I currently
have 30 subtotals.
When I try to add one more I get a the following message:

You have entered too many arguements for this function. It then directs me
to the = sign on the formula bar.

How do I update this without combining some of the earlier subtotals

Thanks - PJ

If you are generating (or could generate) your SUBTOTALS using the SUBTOTAL
worksheet function, then if there are other subtotals within ref1, ref2,… (or
nested subtotals), these nested subtotals are ignored to avoid double counting.

I believe that the included SUBTOTALS are identified by the fact that they are
generated by using the SUBTOTAL function (rather than by using the SUM
function).

The SUBTOTAL(9,cell_ref) function does not include hidden rows, and does not
include nested subtotals, but otherwise should function similarly to the SUM
function.


--ron