SUM Exceeding 30 Columns

[Chris Gamble-4]

Just ran into an error yesterday telling me that the sum function can
only support 30 arguments. I am trying to calculate the Grand Total of
several sub totals, so doing this as a range is not effective. Does
anyone know a work around for this?

thanks

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Anthony Andrews]

Do not know if it will work in your application but have you looked at the SUMPRODUCT() function? This can be used to calculate the totals of a number of arrays or ranges but it is again limited to the 30 argument maximum.

Further, have you tried using brackets to 'fool' the SUM() finction? Again, I do not know if it would work but you could try;

SUM((A1 + B1 + C1), (D1 + E1), (F1 + G1)) etc.......

Have not tried this so I expect Excel will simply swear at you but it would be easy enough to test.

--- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
From: Chris Gamble <cgamble@...>
Subject: SUM Exceeding 30 Columns
To: user@...
Date: Monday, July 21, 2008, 8:25 AM

Just ran into an error yesterday telling me that the sum function can
only support 30 arguments. I am trying to calculate the Grand Total of
several sub totals, so doing this as a range is not effective. Does
anyone know a work around for this?

thanks

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...


     

[Anthony Andrews]

Ignore that, will not work!!!

The other thought that sprang to mind was could you use intermediate cells to hold running totals for you so to speak? So, in one cell you would enter a sum() formula to calculate the total of a number of cells, in another a second sum() formula to calculate the total of another series of cells and in a third cell a sum() formula to simply add together the calculated products of these two intermediate cells. I would guess that you could even use a an additional sheet to hold these intermediate cewlls if necessary.

--- On Mon, 7/21/08, Anthony Andrews <pythonaddict@...> wrote:
From: Anthony Andrews <pythonaddict@...>
Subject: Re: SUM Exceeding 30 Columns
To: "POI Users List" <user@...>
Date: Monday, July 21, 2008, 8:58 AM

Do not know if it will work in your application but have you looked at the
SUMPRODUCT() function? This can be used to calculate the totals of a number of
arrays or ranges but it is again limited to the 30 argument maximum.

Further, have you tried using brackets to 'fool' the SUM() finction?
Again, I do not know if it would work but you could try;

SUM((A1 + B1 + C1), (D1 + E1), (F1 + G1)) etc.......

Have not tried this so I expect Excel will simply swear at you but it would be
easy enough to test.

--- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
From: Chris Gamble <cgamble@...>
Subject: SUM Exceeding 30 Columns
To: user@...
Date: Monday, July 21, 2008, 8:25 AM

Just ran into an error yesterday telling me that the sum function can
only support 30 arguments. I am trying to calculate the Grand Total of
several sub totals, so doing this as a range is not effective. Does
anyone know a work around for this?

thanks

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...


     

[Chris Gamble-4]

Thanks, will try. Is this a limitation in POI or excel. I just did 50
arguments in Open Office and no error. Dont have excel..

Anthony Andrews wrote:

> Ignore that, will not work!!!
>
> The other thought that sprang to mind was could you use intermediate cells to hold running totals for you so to speak? So, in one cell you would enter a sum() formula to calculate the total of a number of cells, in another a second sum() formula to calculate the total of another series of cells and in a third cell a sum() formula to simply add together the calculated products of these two intermediate cells. I would guess that you could even use a an additional sheet to hold these intermediate cewlls if necessary.
>
> --- On Mon, 7/21/08, Anthony Andrews <pythonaddict@...> wrote:
> From: Anthony Andrews <pythonaddict@...>
> Subject: Re: SUM Exceeding 30 Columns
> To: "POI Users List" <user@...>
> Date: Monday, July 21, 2008, 8:58 AM
>
> Do not know if it will work in your application but have you looked at the
> SUMPRODUCT() function? This can be used to calculate the totals of a number of
> arrays or ranges but it is again limited to the 30 argument maximum.
>
> Further, have you tried using brackets to 'fool' the SUM() finction?
> Again, I do not know if it would work but you could try;
>
> SUM((A1 + B1 + C1), (D1 + E1), (F1 + G1)) etc.......
>
> Have not tried this so I expect Excel will simply swear at you but it would be
> easy enough to test.
>
> --- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
> From: Chris Gamble <cgamble@...>
> Subject: SUM Exceeding 30 Columns
> To: user@...
> Date: Monday, July 21, 2008, 8:25 AM
>
> Just ran into an error yesterday telling me that the sum function can
> only support 30 arguments. I am trying to calculate the Grand Total of
> several sub totals, so doing this as a range is not effective. Does
> anyone know a work around for this?
>
> thanks
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>
>      
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Peter Arrenbrecht]

Maybe I'm missing something obvious here but won't

  (A1+B1+C1) + (D1+E1) + ...

work?

On Mon, Jul 21, 2008 at 5:58 PM, Anthony Andrews <pythonaddict@...> wrote:

> Do not know if it will work in your application but have you looked at the SUMPRODUCT() function? This can be used to calculate the totals of a number of arrays or ranges but it is again limited to the 30 argument maximum.
>
> Further, have you tried using brackets to 'fool' the SUM() finction? Again, I do not know if it would work but you could try;
>
> SUM((A1 + B1 + C1), (D1 + E1), (F1 + G1)) etc.......
>
> Have not tried this so I expect Excel will simply swear at you but it would be easy enough to test.
>
> --- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
> From: Chris Gamble <cgamble@...>
> Subject: SUM Exceeding 30 Columns
> To: user@...
> Date: Monday, July 21, 2008, 8:25 AM
>
> Just ran into an error yesterday telling me that the sum function can
> only support 30 arguments. I am trying to calculate the Grand Total of
> several sub totals, so doing this as a range is not effective. Does
> anyone know a work around for this?
>
> thanks
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Anthony Andrews]

Excel imposes the 30 argument limit.

Having read Peter's reply, that seems the most logical and simple IMO. It bypasses the SUM() function entirely and would do the same job (kicks himself for not thinking of it!!).

--- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
From: Chris Gamble <cgamble@...>
Subject: Re: SUM Exceeding 30 Columns
To: "POI Users List" <user@...>
Date: Monday, July 21, 2008, 9:46 AM

Thanks, will try. Is this a limitation in POI or excel. I just did 50
arguments in Open Office and no error. Dont have excel..

Anthony Andrews wrote:
> Ignore that, will not work!!!
>
> The other thought that sprang to mind was could you use intermediate cells
to hold running totals for you so to speak? So, in one cell you would enter a
sum() formula to calculate the total of a number of cells, in another a second
sum() formula to calculate the total of another series of cells and in a third
cell a sum() formula to simply add together the calculated products of these
two intermediate cells. I would guess that you could even use a an additional
sheet to hold these intermediate cewlls if necessary.
>
> --- On Mon, 7/21/08, Anthony Andrews <pythonaddict@...> wrote:
> From: Anthony Andrews <pythonaddict@...>
> Subject: Re: SUM Exceeding 30 Columns
> To: "POI Users List" <user@...>
> Date: Monday, July 21, 2008, 8:58 AM
>
> Do not know if it will work in your application but have you looked at the
> SUMPRODUCT() function? This can be used to calculate the totals of a
number of
> arrays or ranges but it is again limited to the 30 argument maximum.
>
> Further, have you tried using brackets to 'fool' the SUM()
finction?
> Again, I do not know if it would work but you could try;
>
> SUM((A1 + B1 + C1), (D1 + E1), (F1 + G1)) etc.......
>
> Have not tried this so I expect Excel will simply swear at you but it
would be

> easy enough to test.
>
> --- On Mon, 7/21/08, Chris Gamble <cgamble@...> wrote:
> From: Chris Gamble <cgamble@...>
> Subject: SUM Exceeding 30 Columns
> To: user@...
> Date: Monday, July 21, 2008, 8:25 AM
>
> Just ran into an error yesterday telling me that the sum function can
> only support 30 arguments. I am trying to calculate the Grand Total of
> several sub totals, so doing this as a range is not effective. Does
> anyone know a work around for this?
>
> thanks
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>
>      
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...