Extend the number of arguments of a function

  • Thread starter Thread starter JP Ronse
  • Start date Start date
J

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
 
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
 
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
 
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
 
Thanks to all: Bernard, Pete, Biff & Billy for the valuable tips.

You gave me enough inspiration to solve my issue.

Wkr,

JP
 
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
 
Hi,

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