+ Reply to Thread
Results 1 to 14 of 14

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
    242

    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
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,608

    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!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    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 Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,670

    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
    242

    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 Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,670

    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
    242

    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
    MS-Off Ver
    Excel 2019
    Posts
    197

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

    Re: Formula required for calculating sum from many worksheets.

    Or INDIRECT RC Style

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

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,093

    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
    242

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,958

    Re: Formula required for calculating sum from many worksheets.

    Please try at AK7

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

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

    Re: Formula required for calculating sum from many worksheets.

    Hello Bo_Ry,
    You made my day. It works perfectly fine.

    My apologies for late reply.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,608

    Re: Formula required for calculating sum from many worksheets.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. 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. Replies: 3
    Last Post: 03-27-2015, 03:39 AM
  2. Help: Required for SLA calculating formula
    By Techy321 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2014, 04:21 AM
  3. Urgent Help required with Formulas for Calculating Commission
    By ALLYB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-16-2013, 02:41 AM
  4. Help required for calculating profit margins please.
    By Allgermanparts in forum Excel General
    Replies: 6
    Last Post: 07-02-2012, 07:22 AM
  5. Replies: 6
    Last Post: 08-14-2009, 10:16 AM
  6. Calculating a Required Date According to Two Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 11:49 AM
  7. Replies: 0
    Last Post: 03-15-2005, 10:06 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