Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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!!!
0 
Why not just do a 3D reference?
=SUM(Sheet1:Sheet31!B10)
That seems simpler to me.
Soylent Green is PEOPLE!!!
0
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.
0 0
"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
0
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
0
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!!!
0
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.
0
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)
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
