I get an error for a simply sum function.

February 25, 2012 at 10:56:48
Specs: Windows Vista

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.


See More: I get an error for a simply sum function.

Report •


#1
February 25, 2012 at 13:06:44
It would be helpful if we knew what error message your getting.

Summing 38 cells should not be a problem.

MIKE

http://www.skeptic.com/


Report •

#2
February 25, 2012 at 21:31:46
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.


Report •

#3
February 26, 2012 at 07:14:54
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?

Report •

Related Solutions

#4
February 26, 2012 at 10:24:30
I would have to hide those 2 subtotal cells

Why 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

http://www.skeptic.com/


Report •

#5
February 26, 2012 at 14:44:06
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.

Report •

#6
February 26, 2012 at 14:58:35
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

http://www.skeptic.com/


Report •

#7
February 26, 2012 at 15:04:04
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

http://www.skeptic.com/


Report •

#8
February 26, 2012 at 17:06:40
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.


Report •


Ask Question