calculate total from multi sheets

**Goodstart14** · 07-26-2014, 12:55 AM

Hi,
I need to multiply cell C10 and E10 in each worksheet in my workbook to get total hours in Main sheet in the same workbook.
I got 30 worksheets in my workbook and these are identical.
I need to fill cell C10 in the Main sheet.
The formula is;
=Sum(Sheet1!C10*Sheet1!E10+Sheet2!C10*Sheet2!E10+Sheet3!C10*Sheet3!E10.......until the last sheet)

Is there any other way?
It will be broken if I change the sheet name and add a sheet and delete a sheet.

I know if I get a total from one cell , then formula will be
=Sum(Sheet1:Sheet30!C10)

Is there any way possible in excel that I can achieve this in formula, or Can I use a macro to do this?

Thanks

**FDibbins** · 07-26-2014, 01:36 AM

why not just...
=Sum(Sheet1:Sheet30!C10)*=Sum(Sheet1:Sheet30!E10)

if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs. This way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though The formula then becomes...

=Sum(Start:End!C10)*Sum(Start:End!E10)

**Goodstart14** · 07-26-2014, 01:50 AM

In cell C10 in each sheet , I put 'Regular hours' and in cell E10 in each sheet, I put 'Days'.
So suppose to be like below;
=Sum(Sheet1!C10*Sheet1!E10+Sheet2!C10*Sheet2!E10+Sheet3!C10*Sheet3!E10.......until the last sheet)

Please Login or Register  to view this content.

this code will come out different result.

How can I solve this problem?

Thanks!

**:) Sixthsense :)** · 07-26-2014, 02:01 AM

Try this...

=SUMPRODUCT(SUMIF(INDIRECT("'"&"Sheet" & ROW(INDIRECT("1:30")) & "'!C10"),"<>""")*SUMIF(INDIRECT("'"&"Sheet" & ROW(INDIRECT("1:30")) & "'!e10"),"<>"""))

**Goodstart14** · 07-26-2014, 02:31 AM

Could you explain little more for me?
Sorry.
What is

Please Login or Register  to view this content.

this meaning?

Thanks!

**:) Sixthsense :)** · 07-26-2014, 02:51 AM

I don't know how I am going to explain it for you, because Evaluate (Under Formulas Menu) button will explain it in a better way than my explanation, anyhow I will try to do that

ROW(INDIRECT("1:30")) will result

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}

"'"&"Sheet" & ROW(INDIRECT("1:30")) adding Default Sheet Name will be converted as

'Sheet & {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30} & '!C10

{'Sheet1;'Sheet2;'Sheet3;'Sheet4;'Sheet5;'Sheet6;'Sheet7;'Sheet8;'Sheet9;'Sheet10;'Sheet11;'Sheet12;'Sheet13;'Sheet14;'Sheet15;'Sheet16;'Sheet17;'Sheet18;'Sheet19;'Sheet20;'Sheet21;'Sheet22;'Sheet23;'Sheet24;'Sheet25;'Sheet26;'Sheet27;'Sheet28;'Sheet29;'Sheet30}

& "'!C10" adding Cell Reference

{'Sheet1'!C10;'Sheet2'!C10;'Sheet3'!C10;'Sheet4'!C10;'Sheet5'!C10;'Sheet6'!C10;'Sheet7'!C10;'Sheet8'!C10;'Sheet9'!C10;'Sheet10'!C10;'Sheet11'!C10;'Sheet12'!C10;'Sheet13'!C10;'Sheet14'!C10;'Sheet15'!C10;'Sheet16'!C10;'Sheet17'!C10;'Sheet18'!C10;'Sheet19'!C10;'Sheet20'!C10;'Sheet21'!C10;'Sheet22'!C10;'Sheet23'!C10;'Sheet24'!C10;'Sheet25'!C10;'Sheet26'!C10;'Sheet27'!C10;'Sheet28'!C10;'Sheet29'!C10;'Sheet30'!C10}

**Goodstart14** · 07-26-2014, 03:09 AM

Thanks,
I got 30 sheets right now, but this will be changed depending on project. Sometimes, I got 20 sheets, or less, or more.
So I made 'Start' and 'End' sheets that have nothing in it.
I made formulas in Main sheet and this Main sheet will be fine evenif I add sheets or delete sheets because I add sheets after 'Start' sheet, so the main sheet will be fine.

But this C10 in the Main, I have to multiply each C10 and E10 in each sheet , and then put this data in cell C10 in the Main.
If I add a sheet between 'Start' and 'End'sheet, then, I will change the new sheet name to city name from 'Site'.
I hide a sheet(named 'Site') and I use this for adding a new sheet.
So what I want is that I need a formula that keep the Main sheet (not broken) evenif I add or delete.
I hope this explanation help you understand.
Should I use VBA? but, I am beginner.

Thanks

**:) Sixthsense :)** · 07-26-2014, 03:16 AM

I suggest you to allot a single (CONSTANT) cell (For Example A1) in everysheet to do the calculation like c10*e10.

Then you can use the below formula in Main sheet

=Sum(Start:End!A1)

**oeldere** · 07-26-2014, 04:03 AM

Please Login or Register  to view this content.

What does each sheet respresent => dates, names, project?

**Goodstart14** · 07-26-2014, 07:40 AM

Project.

Thanks

calculate total from multi sheets

LinkBack

Thread Tools

Rate This Thread

Display

calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Re: calculate total from multi sheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need a formula to calculate all total purchase from different sheets by acct number

[SOLVED] Calculate total from two sheets

[SOLVED] Need to calculate a total from a range of cells on different sheets

Calculate Year-to-Date total in from multiple sheets

[SOLVED] How do I calculate Daily Sales for multi-day, multi-tech jobs?

Bookmarks

Bookmarks

Posting Permissions