This problem is very ridiculous...but I couldn't solve it the way I wanted to. I am working on a spreadsheet in which every sheet has a daily report. I have to add up the value of each sheet in a separate summary sheet. However, the SUM function adds up only 30 individual cells (1 from each sheet) at a time. What in case of months with 31 days? I get stuck there.

I read this solution in an older post (it did not allow me to re-post since it was in 2003 and there were little chances of someone reading my reply.) but it does not seem to work for me:

"Calculate the 31 days in two different cells (letts say the first 15 days of the month and the last 16) and add those together."

When I do this, the cell gives me a #VALUE error :(. The formula I entered is pasted below. Is it incorrect?

=SUM('1'!B10,'2'!B10,'3'!B10,'4'!B10,'5'!B10,'6'!B10,'7'!B10,'8'!B10,'9'!B10,'10'!B10,'11'!B10,'12'!B10,'13'!B10,'14'!B10,'15'!B10,('16'!B10,'17'!B10,'18'!B10,'19'!B10,'20'!B10,'21'!B10,'22'!B10,'23'!B10,'24'!B10,'25'!B10,'26'!B10,'27'!B10,'28'!B10,'29'!B10,'30'!B10,'31'!B10))

Please help me :(

First of all, you have an additional left paren in your formula right before sheet 16. Do you want that there? Soylent Green is PEOPLE!!!

Why not just do a 3D reference? =SUM(Sheet1:Sheet31!B10)

That seems simpler to me.

Soylent Green is PEOPLE!!!

Maybe I'm on the wrong track here but... Why not have a separate formula in another cell to take the 1st result from 1 to 30 and sumif sheet 31 B10>0.

Oh, I forgot. Use the formula Jennifer gave you, it is much simpler and will do the same thing.

"Use the formula Jennifer gave you, it is much simpler and will do the same thing." How can you go wrong, her last name is

SUMN:)

Bryan

LOL :) Soylent Green is PEOPLE!

Its a "sum" function! If it was an average or something, then you'd have to be a bit clever, but really... you could just add the cells rather than SUMming them! ='1'!B10+'2'!B10+'3'!B10+'4'!B10.....

or if you really, really like SUM functions, then terminate the first after 30 parameters, type a "+" and then start another SUM function.

And if you are a complete SUM fancier, then you could nest the SUMs:

=sum(sum(ref1, ref, ref3), sum (ref4, ref5, ref6))

Silliness. It must be summertime.Tom

OR....use the formula I typed so that one doesn't have to go from sheet to sheet to sheet to sheet...and so on. Soylent Green is PEOPLE!!!

Well, thank you everyone. I'll try nesting (that is what I was trying, but I didn't know one had to add 'SUM' before each nest) once since it's given me so many headaches.

Then I will stick to Jennifer's suggestion - seems to be the best to me :)

A Certan TH: how does not being an ace with excel translate into silliness? There are several things I can do that you cannot even begin! That does not (in my eyes) make you silly.

We seem to have crossed wires here: My response was silly, not your question. Still, always good to note that people know exactly who I am and what I am capable of from just the few words I write here. (Now that IS a silly thing to say)

Ask Your Question

Weekly Poll

Do you believe Microsoft when it says it views the "Desktop" as the core of Windows?

Discuss in The Lounge

Poll History