Sum all worksheets to a Single Worksheet
Results 1 to 5 of 5

Thread: Sum all worksheets to a Single Worksheet

  1. #1
    Board Regular
    Join Date
    May 2008
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Sum all worksheets to a Single Worksheet

    I have multiple worksheets. In cell "M3" they all have the following formula adding up columns within it:

    =IF(R3,R3+S3+T3," ")

    On the Summary page I have this formula trying to add up all the worksheets but it's returning a VALUE. I want it to return a sum for all the worksheets in that cell (some weeks may NOT have a number). If none of the cells have a value then it should just return a blank cell. What I think should be easy just isn't working

    =IF(OR('Week 42'!M3>0,'Week 44'!M3>0,'Week 46'!M3>0,'Week 48'!M3>0,'Week 50'!M3>0,'Week 52'!M3>0),('Week 42'!M3+'Week 44'!M3+'Week 46'!M3+'Week 48'!M3+'Week 50'!M3+'Week 52'!M3)," ")

  2. #2
    Board Regular
    Join Date
    May 2008
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum all worksheets to a Single Worksheet

    Slight change:

    Each of my 6 worksheets has a formula:
    =IF(OR(R3>0,S3>0,T3>0),(R3+S3+T3), " ")

    This formula, IF any of three cells R3, S3, T3 have numbers in them it will add them or it will return a BLANK (it's too late to add to my first POST above)

    There are 6 Sheets. The SAME cell on each sheet has this formula. I want to add ALL cells containing a number and consolidate it on a single summary sheet. IF there is NO data then I want it to return a blank.

    The formula I tried isn't working. NOTE some sheets may not have data.

  3. #3
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,664
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sum all worksheets to a Single Worksheet

    Assuming your Summary sheet is either the extreme left sheet tab or the extreme right sheet tab and your other tabs are, left to right.... Sheet2 to Sheet7 or whatever their names then try

    =IF(ISERROR(1/SUM(Sheet2:Sheet7!M3)),"",SUM(Sheet2:Sheet7!M3))

    Hope that helps.
    Last edited by Snakehips; Jul 12th, 2019 at 04:38 PM. Reason: Can't count
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,475
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum all worksheets to a Single Worksheet

    A few additional notes to Snakehips' post. You can create a 3-D SUM formula like he showed, but the sheets must all be consecutive. It looks like you're trying to add up Week 42, Week 44, etc. If you have a Week 43 in there, your totals will be off, unless you want to rearrange the sheets. Also, check the example in O3 below to see how to code it when the sheet names have spaces in them.
    OPQ
    37.5Week 42
    47.5Week 44
    57.5Week 52

    Week 42



    Worksheet Formulas
    CellFormula
    O3=SUM('Week 42:Week 52'!M3)
    O4=SUMPRODUCT(SUMIF(INDIRECT("'"&$Q$3:$Q$5&"'!M3"),">0"))
    O5=IFERROR(1/(1/SUMPRODUCT(SUMIF(INDIRECT("'"&$Q$3:$Q$5&"'!M3"),">0"))),"")



    Finally, another option is to list the sheet names you want to include, then use a SUMPRODUCT formula to get the sum from those sheets (formula in O4). This version allows you to use the sheets in any order or position. Finally, another way to get a 0 to appear as an empty cell is shown in O5.

    Hope this helps!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    Board Regular
    Join Date
    May 2008
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum all worksheets to a Single Worksheet

    Thank you for all your replies.
    The following is working perfectly:

    =IF(SUM('Week 42:Week 52'!S3)>0,SUM('Week 42:Week 52'!S3)," ")

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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