Is there a workaround when hitting the maximum summands in =sum in an Excel formula?

Question

I am hitting a limit when I try to assign a sum to a cells formula:

cells(y,x).formula = "=sum(G65,H74,I38,J20,G22, .... ,K19,L22)"

It seems as though I can't have more than 30 summands in a =sum formula.

Is there a workaround for this problem?

Answer 1

You could group the cells by defining names for them or split the sum-formula into =sum(...) + sum(...).

Answer 2

Use pluses instead.

Answer 3

I would add a column to be used just for summing. It would contain a formula that pulls each row's number into the new column and then your sum formula for the code would look like this:

cells(y,x).formula = "=sum(Q1:Q100)"

Where Q1:Q100 would be the new column with the numbers to be summed.

Answer 4

You can cascade the sums as well:

cells(y,x).formula = "=sum(sum(G65,H74,...,I38,J20), sum(G22, .... ,K19,L22))"

Keep in mind that the formula length limit for Excel is 1,024 characters, so you might run into that limit if your formula needs to be much longer. If so, use other cells to act as intermediate formulas and sum them.

Answer 5

Options:

• A1 + A2 + ... + An
• Sum(A1,A2) + ... + Sum(An, Am)
• Sum(A1,A2,Sum(An,Am))

The theird option doesn't use an plus-signs, in case you're alergic.

Answer 6

Your example doesn't show it, but if all of your cells are in the same row or column and you're selecting individual sums to avoid intermediate calculations, consider using SUBTOTAL. See http://www.dailydoseofexcel.com/archives/2008/01/04/subtotals-ignoring-subtotals/