Totaling worksheet data using SUM? 1

[hangmeup]

I'm creating a workbook to calculate my organization's sales, with a separate sheet for each sales rep and the top sheet will have the totals from all the separate sheets.

Is there a sum formula I can use to add up items from each rep's sheet that will update if I insert another sheet (sales rep) to the workbook? I can't get SUM to work, and I can't find anything in Excel help or FAQ/Keyword search.

Thanks for your help!

 

[SQLBI]

Hi,

=SUM(Sheet1:Sheet10!A1)

will total up cell A1 for sheets 1 to 10, however if you're adding new sheets, they will have to be after Sheet1 and before Sheet10 for the sum to work.

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[bluedragon2]

Lets say you want to total the following items:

Sheet2 Cells A1 through A6
Sheet3 Cells B1 through B6

your formula would look like this:

=SUM(Sheet2!A1:A6,Sheet3!B1:B6)

If you want to sum just one cell on those sheets say A7 and B7 then you can do:

=Sheet2!A7+Sheet3!B7

When you add a new sheet to the workbook, you will have to update your formulas to inclide it. The only way to do it dynamically is by useing code.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

good one Leigh



Blue

If I wasn't Blue, I would just be a Dragon...

 

[hangmeup]

Thanks so much! I can see what I was doing wrong now. Stars for great answers and such quick response!

T