Hi All,

A lot of functions have a limitation on the number of arguments/values, e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing to
overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

The formula =SUM(A1:A10000) has only ONE argument
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Hi Bernard,

Of course, perhaps I was not clear enough.

In most cases 30 arguments will be more then enough because most of the
arguments can be written as a range.

But suppose I have a huge workbook with more then 30 sheets and I have to
sum a value from each sheet. Then I can't write it anymore as range. I know
there are workarounds to get the result but was just looking fora way to
overrule the limitation when using cells just as you can do with values by
enclosure them with {}.

Wkr,

JP




"Bernard Liengme" wrote in message
...
The formula =SUM(A1:A10000) has only ONE argument
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Well, if you could arrange that the cells you wanted to sum are the
same on each of those sheets (eg Z1, which may contain a formula to
pick up from the actual cell in each sheet), then you could have:

=SUM(first:last!Z1)

where first and last are the outer sheets of a "sandwich". There could
be more than 30 sheets in the sandwich.

Hope this helps.

Pete

On Sep 4, 4:49*pm, "JP Ronse" wrote:
Hi Bernard,

Of course, perhaps I was not clear enough.

In most cases 30 arguments will be more then enough because most of the
arguments can be written as a range.

But suppose I have a huge workbook with more then 30 sheets and I have to
sum a value from each sheet. Then I can't write it anymore as *range. I know
there are workarounds to get the result but was just looking fora way to
overrule the limitation when using cells just as you can do with values by
enclosure them with {}.

Wkr,

JP

"Bernard Liengme" wrote in message

...



The formula =SUM(A1:A10000) has only ONE argument
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1*,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP- Hide quoted text -

- Show quoted text -

Pete's answer is great. At a pinch
=sum(ref1, ref2, ref30)+sum(ref31,ref32...ref64)
Or switch to Excel 2007 (no! wait for Excel 2010) with argument limit of 255
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JP Ronse" wrote in message
...
Hi Bernard,

Of course, perhaps I was not clear enough.

In most cases 30 arguments will be more then enough because most of the
arguments can be written as a range.

But suppose I have a huge workbook with more then 30 sheets and I have to
sum a value from each sheet. Then I can't write it anymore as range. I
know there are workarounds to get the result but was just looking fora way
to overrule the limitation when using cells just as you can do with values
by enclosure them with {}.

Wkr,

JP




"Bernard Liengme" wrote in message
...
The formula =SUM(A1:A10000) has only ONE argument
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Try it like this:

=SUM((A1,D1,G1,B4,S1,X2))

The inner ( ) are 1 argument.

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

You can combine two SUM Functions e.g.
=SUM(C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C1 4,C15,C16,C17,C18,C19,C20)+SUM(D1,D2,D3,D4,D5,D6,D 7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D2 0)

40 Cells

HTH
Peter

"JP Ronse" wrote:

Hi All,

A lot of functions have a limitation on the number of arguments/values, e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing to
overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Thanks to all: Bernard, Pete, Biff & Billy for the valuable tips.

You gave me enough inspiration to solve my issue.

Wkr,

JP

"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Hi,

And here is another tip: Excel 2007 allows 255 arguments unlike 2003's 30.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JP Ronse" wrote:

Thanks to all: Bernard, Pete, Biff & Billy for the valuable tips.

You gave me enough inspiration to solve my issue.

Wkr,

JP

"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this doesn't
work.

Wkr,

JP

Thanks Shane, but I'm still on E2003 and upgrades are a company policy ...

Wkr,

JP
"Shane Devenshire" wrote in
message ...
Hi,

And here is another tip: Excel 2007 allows 255 arguments unlike 2003's
30.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JP Ronse" wrote:

Thanks to all: Bernard, Pete, Biff & Billy for the valuable tips.

You gave me enough inspiration to solve my issue.

Wkr,

JP

"JP Ronse" wrote in message
...
Hi All,

A lot of functions have a limitation on the number of arguments/values,
e.g.

=SUM(cell_1, cell_2, ...., cell_30). (non-adjacent cells)

The same limitation exists if values are used:

=SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1)

But you can overrule the limitation if you write the function as:

=SUM({1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1})
= 40

My question is if there is a way to write functions with ranges
allowing
to overrule the limitation?

I've tried already something like =SUM({cell_1,cell_2}) but this
doesn't
work.

Wkr,

JP