Sum cell value across multiple worksheets.

  • Thread starter Thread starter Damian
  • Start date Start date
D

Damian

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you
 
My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
 
how do you hide a worksheeet? I can not figure it out. Is it right in front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you
 
It sure is. Right click the sheets tab...
Micky


Damian said:
how do you hide a worksheeet? I can not figure it out. Is it right in front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you
Click to expand...
 
Hide: Select the sheet, then Format>Sheet>Hide

If you setup the sheets at the start and end and then hide them, and new
sheets will always be between them.

HTH

Bob
 
Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you
 
Pre XL2007 Format | Sheet Hide
Xl2007 right click the sheet tab...

So long as you are adding up ALL sheets this is relatively foolproof. Any
worksheets inserted will be after the start sheet but before the end sheet.
 
To hide a worksheet Format > Sheet > Hide.
To unhide do the same and select Unhide.

When you hide the Start and End any sheets you insert will be between them.

HTH
Regards,
Howard
 
=IF(C2=1,SUM(First:Last!A14),"")

HTH
Peter

Damian said:
Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you
Click to expand...
 
Hi, I thought this would do the trick, but it did not because I stated it
wrong.

What i would like to do is:
ex. I have sheet 1 through sheet 5 (plus the hidden First:Last)
In some of these sheets in cell "C2" I would like to put eather 1 and have a
function that woud sum it up.

so If I put 1 in sheet1 and sheet3 the function should add only these two.

something like this(I know it is wrong, but gives an idea)
=IF((First:Last!C2=1),SUM(First:Last!A14),"")

is this possible?

Thank you.
 
Back
Top