+ Reply to Thread
Results 1 to 12 of 12

Formula required for calculating sum from many worksheets.

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    158

    Formula required for calculating sum from many worksheets.

    Dear Team,

    PFA

    I would to calculate the Sum from various sheets.
    I have a formula mentioned in C3 of Summary sheet. (It was give by forum only)

    But its very time consuming if I copy paste that formula in approx. 250 columns since formula need to be modified everytime.

    Is it possible to simply this formula since the data base is huge and its time consuming.?

    Please assist.
    Attached Files Attached Files
    Last edited by lalaarif1; 12-17-2018 at 03:12 AM. Reason: File was password protected. RE-attaching the file.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,142

    Re: Formula required for calculating sum from many worksheets.

    Your workbook has password protection and cannot, therefore, be opened.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    158

    Re: Formula required for calculating sum from many worksheets.

    Sorry for inconvenience.

    I have updated the previous post the attached the revised file.

  4. #4
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,331

    Re: Formula required for calculating sum from many worksheets.

    Please try this in C3 of 'Summary' filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    158

    Re: Formula required for calculating sum from many worksheets.

    This may not work since the row sequence is not same in all the sheets.

    Please assist.

  6. #6
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,331

    Re: Formula required for calculating sum from many worksheets.

    lalaarif1

    What that says is that the upload provided is not truly representative of what you are really working with.

    Please upload another example ... warts and all.

    In the meantime we often see the practice of capturing data in sheets and then trying to summarize that data.
    That is backwards to efficient workbook design and will often result in excessive calls to the volatile INDIRECT function.
    If you are not familiar with volatility and its importance this link may be of help.

    http://www.decisionmodels.com/calcsecretsi.htm

    A far better approach would be to gather all data in one sheet as a 2D flat database. This is advantageous because:
    1. It avoids excessive calls to INDIRECT as mentioned above.
    2. You have more options for summarization besides monthly:
    • filters
    • formula based summaries
    • it opens you to the wonderful world of Pivot tables.

  7. #7
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    158

    Re: Formula required for calculating sum from many worksheets.

    Hi,
    The attachment was for sample only.

    In C3, I could have used SUMIF() and copy pasted the same in other column.
    But it takes time in calculating the values.

    Each sheet has approx 20000 row and around 50 columns.
    Sequence of row is not same. but columns heading sequence are same.

    in C3, =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$b:$b"),$B3,INDIRECT("'"&SheetList&"'!c:c"))) works perfectly fine.
    But only issue is I can not copy the same formula in D3 onwards.

    Please assist.

  8. #8
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    Posts
    115

    Re: Formula required for calculating sum from many worksheets.

    Maybe,

    In "Summary" sheet C3, formula copied across and down :

    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$B:$B"),$B3,INDIRECT("'"&SheetList&"'!"&CHAR(66+COLUMNS($A:A))&1)))

    Regards
    Bosco

  9. #9
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,215

    Re: Formula required for calculating sum from many worksheets.

    Or INDIRECT RC Style

    C3
    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2",),$B3,INDIRECT("'"&SheetList&"'!C",)))
    Bo

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,187

    Re: Formula required for calculating sum from many worksheets.

    In C3 of summary sheet then dragged across.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!$b:$b"),$B3,OFFSET(INDIRECT("'"&SheetList&"'!C:C"),0,COLUMNS($C3:C3)-1)))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  11. #11
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    158

    Re: Formula required for calculating sum from many worksheets.

    Thanks a lot for helping with formula.

    PFA the revised file.

    I am facing 1 issue.
    When I inserted few rows and columns in the working data, the formula fails.

    Can you please assist ?

  12. #12
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,215

    Re: Formula required for calculating sum from many worksheets.

    Please try at AK7

    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C3",),$C7,INDIRECT("'"&SheetList&"'!C",)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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