Extend the number of arguments of a function

[JP Ronse]

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

 

[Bernard Liengme]

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

 

[T. Valko]

Try it like this:

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

The inner ( ) are 1 argument.

 

[Billy Liddel]

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

40 Cells

HTH
Peter

 

[JP Ronse]

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

 

[Pete_UK]

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

 

[JP Ronse]

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

You gave me enough inspiration to solve my issue.

Wkr,

JP

 

[Bernard Liengme]

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

 

[Shane Devenshire]

Hi,

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

 

[JP Ronse]

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

Wkr,

JP