+ Reply to Thread
Results 1 to 10 of 10

calculate total from multi sheets

  1. #1
    Forum Contributor
    Join Date
    11-21-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    190

    calculate total from multi sheets

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: calculate total from multi sheets

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    11-21-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: calculate total from multi sheets

    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!

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: calculate total from multi sheets

    Try this...

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


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Forum Contributor
    Join Date
    11-21-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: calculate total from multi sheets

    Could you explain little more for me?
    Sorry.
    What is
    Please Login or Register  to view this content.
    this meaning?

    Thanks!

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: calculate total from multi sheets

    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}

  7. #7
    Forum Contributor
    Join Date
    11-21-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: calculate total from multi sheets

    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

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: calculate total from multi sheets

    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)

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: calculate total from multi sheets

    Please Login or Register  to view this content.
    What does each sheet respresent => dates, names, project?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  10. #10
    Forum Contributor
    Join Date
    11-21-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: calculate total from multi sheets

    Project.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need a formula to calculate all total purchase from different sheets by acct number
    By clifford_ayala in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-05-2013, 11:05 PM
  2. [SOLVED] Calculate total from two sheets
    By Anibha Jain in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-16-2013, 03:03 AM
  3. [SOLVED] Need to calculate a total from a range of cells on different sheets
    By jrosko1 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-22-2012, 12:35 PM
  4. Replies: 2
    Last Post: 12-11-2008, 11:58 AM
  5. [SOLVED] How do I calculate Daily Sales for multi-day, multi-tech jobs?
    By David in forum Excel General
    Replies: 0
    Last Post: 12-14-2005, 06:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1