Strange issue with extremely long SUM formula

Hi,

I use a spreadsheet I designed to keep track of wages, pension, tax and surplus funds which was working well until I ran into this strange problem with a very long SUM formula.

Here's a quick rundown, I need to add together a number of cells which occur in the sequence of every ninth cell (example =SUM(L36+L45+L54+L63+L72+L81+ etc. etc.) and all I've been doing each week is adding the next cell in the sequence.

I pretty much need the spreadsheet to continue indefinitely and now the SUM formula is up to ....L2811+L2823+L2835+L2847) and for some reason it won't let me add more cells to the formula. When I click in the formula bar the cell numbers change colour but when I type in + and then click on the next cell in the sequence the cell numbers turn black in the formula bar and the formula stops working.

I only know the basics and I'm hoping someone with far more knowledge than I have could please tell me there's a better formula I should be using or if there is a way to allow extra long formulas.

Thanks for your help.

Hi and welcome
To make a sum you do not need to use the function ( there is probably a limit to the number of arguments SUM accepts)
Just enter =L36+L45+L54+L63+L72+L81+ etc. etc.
OTOH if the cells are spaced regularly ( in your first example every 9 rows) there are other and shorter possibilities
But your second example seems to show irregular intervals.
Perhaps post a sample sheet ? ( click Go advanced - Manage attachments)

Hi Pepe,

Thanks so much for your reply. I just tried entering = and removing SUM( and ) but it still has the same issue, there seems to be a character or byte size limit that I have hit.

The spacing of the cells changes with the addition of new employees which is why the second sequence of cells is different.

Would you know what other possibilities I could use?

Perhaps changing your layout might help.
Can you post a sample sheet as I asked earlier? (removing any confidential info)

Do you have descriptions in another column that line up with the amounts to sum in each formula?

For example, if you wanted to sum all of the amounts in column L, where there is the word 'Pension' in column J, you could simply use

Formula:

Please Login or Register  to view this content.

without adding each cell to the formula individually.

Hi,
Here is the spreadsheet I designed (without the data) that calculates each employees gross, tax, net, pension and the total cost to me. It shows me what my bank statement should be and then what funds are able to be spent safely, basically just stops me spending funds that are being held for the quarterly tax and pension instalments. I wrote some functions that calculate the appropriate amounts for each employees tax. The spreadsheet has been running since June '14 and needs to continue.

At the moment the cell that has the SUM with the issue is Gross Payments but Tax Payable will soon run into the same issue.

Hope it makes sense.

Continuing to add cells works, it is just too many cell references to show each cell in a different color.

FYI, you can use a SUMIF formula to replace your long SUM formula.

Like this:
=SUMIF(K27:K2931,"Total",L27:L2931)

Originally Posted by Melvosh

In Excel 2007, the number of arguments in a function is limited to 255, which is how many cells you've currently got adding up. That's my guess as to why you're running into trouble.

The way that the OP has written the formula, everything is being passed to the first argument of the function.

Originally Posted by kvsrinivasamurthy

This ARRAY formula works from Column 1 to 16380. Last column is 16384.
(A1+A10+A19+.........)

=SUM(SUMIF(OFFSET(A1,0,9*(ROW($1:$1821)-1)),"<>0"))

Could you explain how that works with the OP's example?
The number of rows between the cells used in the formula varies between 9 and 12, which is why 63falcondude and myself both suggested using sumif with an alternative column for the criteria.

@Melvosh. 2019 has the same limits, so I think it fair to assume that all versions in between would be the same.

If there were too many arguments then entry of the formula would be rejected as opposed to simply having visual anomolies. (try entering =IF(,,,) and see what happens).

The limit on the number of values passed to an individual argument can't be as low, otherwise array formulas would never work.

Think {=SUM(IF(A1:A1000="Some criteria",B1:B1000))}

I can't see anything in the limitations that stands out as being the cause of the problem, the formula is much shorter than than the character limit.

Originally Posted by 63falcondude

Continuing to add cells works, it is just too many cell references to show each cell in a different color.

Yep I just checked and you're absolutely right, it does continue. I thought it wasn't working because it was a dotted cell outline and not the solid coloured one like usual when selecting the next cell.

I've tried the SUMIF but can't seem to figure it out correctly, but it's good the normal SUM will keep working and thanks heaps for pointing it out (stupid me should have looked at the calculation first lol).

Originally Posted by Glenn.

I've tried the SUMIF but can't seem to figure it out correctly,

63falcondude has given you a sumif formula that should work in post #7. As an alternative that you wouldn't need to change every time you add more data, see if this gives the same result as your long formula.

=SUMIF(K:K,"Total",L:L)

Sorry, I missed that exact formula and tried to write one myself without success.

63falcondude's formula =SUMIF(K27:K2931,"Total",L27:L2931) worked perfectly and at first I couldn't figure out how it worked but I think I have just figured it out. Is the formula saying only add the cells in column L together that are preceded by the word Total in column K?

Originally Posted by AliGW

No, it's saying add together the cells in L that contain Total in K.

That's what Glenn. said, if you read post #16 carefully, Ali!

cells in column L together that are preceded by the word Total in column K

Sometimes, understanding the proper use of the language too well can make it harder to understand what we mere mortals are saying.

Not sure that replying to something you misread strictly complies with the definitions of a Freudian slip, but it does seem to be quite a common thing in the forum, so maybe it needs a name

@Glenn.

If you want to try learning / understanding how different formulas / functions work then it might be easier to practice with a different (smaller) file that has less data to try and work with.

Comparing the results of 2 or 3 different formulas is a lot easier with 10 rows of data than it is with 1000 rows.

Originally Posted by Glenn.

63falcondude's formula =SUMIF(K27:K2931,"Total",L27:L2931) worked perfectly and at first I couldn't figure out how it worked but I think I have just figured it out. Is the formula saying only add the cells in column L together that are preceded by the word Total in column K?

Yes. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.