I just need to add subtotals. Is it because there are 38 cells being added together.=SUM(C394,C384,C379,C367,C359,C351,C343,C335,C324,C317,C302,C290,C284,C276,C259,C254,C248,C243,C231,C223,C206,C200,C194,C189,C185,C180,C150,C141,C124,C114,C90,C79,C72,C67,C51,C35,C29,C9)

I know I can subtotal 16 of the subtotal cells and then the other 16 subtotal cells. I could then have a grand total by adding both the subtotal cells together and I would have to hide those 2 subtotal cells and was trying not to have to do that unless it the last option.

It would be helpful if we knew what error message your getting. Summing 38 cells should not be a problem.

MIKE

I assume you are using Excel 2003. That version only allows 30 arguments per function. You have 38.

Try selecting the cells, give the range a name and then sum the named range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Unfortunately, I am using Excel 2000 and the error states, "The formula you typed contains an error" you then have to click Help or OK. Help brings up the "I have an error in my formula" section of the Microsoft Excel Help tutorial and OK just highlights the 30th cell number in the sequence of subtotals. Is there an update, besides purchasing a new version of excell, to have more than 30 cells in a formula?

I would have to hide those 2 subtotal cellsWhy do you have to hide them? and why have 2 cells?

=SUM(C394,C384,C379,C367,C359,C351,C343,C335,C324,C317,C302,C290,C284,C276,C259)+SUM(C254,C248,C243,C231,C223,C206,C200,C194,C189,C185,C180,C150,C141,C124,C114,C90,C79,C72,C67,C51,C35,C29,C9)

Should work.

MIKE

I only want one total at the bottom of the column of the 397 rows in the spreadsheet and that is the exact formula I have inputed into the cell and that's when I get the error message and the 30th cell (C90) is highlighted in gray.

Ok, so try: =SUM(C394,C384,C379,C367,C359)+SUM(C351,C343,C335,C324,C317)+SUM(C302,C290,C284,C276,C259)+SUM(C254,C248,C243,C231,C223)+SUM(C206,C200,C194,C189,C185)+SUM(C180,C150,C141,C124,C114)+SUM(C90,C79,C72,C67,C51)+SUM(C35,C29,C9)

MIKE

Or just do away with the =SUM(): =C394+C384+C379+C367+C359+C351+C343+C335+C324+C317+C302+C290+C284+C276+C259+C254+C248+C243+C231+C223+C206+C200+C194+C189+C185+C180+C150+C141+C124+C114+C90+C79+C72+C67+C51+C35+C29+C9

MIKE

OK, so Excel 2000 or 2003 - it doesn't matter. Only 30 arguments are allowed within a given function in those versions.

That is why Excel is highlighting the first "illegal" argument.

Instead of trying to write a really long formula, try this:

1 - Select your 38 cells by using the Ctrl key as you click in each cell.

2 - Once they are all selected, click in the box above Column A and enter a Name for the range - e.g. MySubtotals - and then press Enter. You must press enter while your cursor is still in the name box in order for the name to take effect.

3 - Use =SUM(MySubtotals) as your formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History