+ Reply to Thread
Results 1 to 4 of 4

Sum across 52 Sheets and Sum Cells D13 thru D19

  1. #1
    Registered User
    Join Date
    01-11-2008
    Posts
    2

    Sum across 52 Sheets and Sum Cells D13 thru D19

    Sum across 52 Sheets and Sum Cells D13 thru D19 per sheet depending if the quarter ends within a given week else just read Cell D 20:

    This is to sum all hours within a quarter in a Workbook that has a time sheet for each day e.g week 1 thru week52


    June 1 thru 31 Aug sum hours accross sheets for these weeks
    Sept 1 thru 30 Nov sum hours accross sheets for these weeks
    Dec 1 Thru 28 Feb sum hours accross sheets for these weeks
    Mar 1 Thru 31 may sum hours accross sheets for these weeks

    Cell D20 contains the sum of the hours per each day (Sheet)
    Cell d13 thru D19 Contain daily hours

    e.g

    C13 thru C19 D13 thru D19
    26/7/2007 3.00
    27/07/2007 5.00
    28/07/2007 5.00
    29/07/2007 8.00
    30/07/2007 10.00
    31/07/2007 8.00
    1/08/2007 8.00
    the below works but is very tedious to setup for the whole workbook

    Does anyone know of a way to shorten this or make it simpler... it works as is .. see attached zip of the Workbook (its a work in progress)

    Sum Cells D13 thru D19 per sheet depending if the quarter ends within a given week else just read Cell D 20:

    You should look in the QTR graph sheet Cell D3 to see this formula

    =SUM(Week45!D16:Week45!D19,Week46!D20,Week47!D20,Week48!D20,Week49!D20,Week50!D20,Week51!D20,Week52!D20,Week1!D20,Week2!D20,Week3!D20,Week4!D20,Week5!D20,Week6!D13,Week6!D14)*16.19%
    Attached Files Attached Files
    Last edited by bindu; 01-11-2008 at 01:31 AM.

  2. #2
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Hopefully I understood you correctly and maybe I over did this but Look at the attachment below. I use this formula in worksheet Qtr Graph in cells D3:D6.

    The formula below is an-array need to hold down

    Ctrl,Shift,Enter

    to get the correct results.

    =SUM(IF(TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(wks))))>=MATCH("Week1",wks,0),IF(N(OFFSET(INDIRECT("'"&wks&"'!C13:C19"),ROW(INDIRECT("13:19"))-13,,1))>=B3,IF(N(OFFSET(INDIRECT("'"&wks&"'!C13:C19"),ROW(INDIRECT("13:19"))-13,,1))<=C3,N(OFFSET(INDIRECT("'"&wks&"'!D13:D19"),ROW(INDIRECT("13:19"))-13,,1))))))*Settings!D$3


    Hope it helps!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-11-2008
    Posts
    2
    Thanks for the prompt reply and for your answer here is what i think might be a simpler answer:

    with respect steven

    From this :

    =SUM(Week10!D15:Week10!D19,Week11!D20,Week12!D20,Week13!D20,Week14!D20,Week15!D20,Week16!D20,Week17!D20,Week18!D20,Week19!D20,Week20!D20,Week21!D20,Week22!D20,Week23!D13,Week23!D14)*32

    Check Qtr Graph Cell D4

    to this:
    =SUM(Week10!D15:Week10!D19,Week11:Week22!D20,Week23!D13,Week23!D14)*Settings!D3

    note
    ek10!D15:Week10!D19 << specifies a cell range

    Week11:Week22!D20 << Specifies a Sheet range

    Week11:Week22!D20 Refers to the sheets where the Cell (D20) is the Cell we want to read in all sheets E.G the whole week is included in the quarter on the sheets between week11 and 22
    Attached Files Attached Files

  4. #4
    all4excel
    Guest

    Smile Dynamic addition

    Quote Originally Posted by bindu
    Thanks for the prompt reply and for your answer here is what i think might be a simpler answer:

    with respect steven

    From this :

    =SUM(Week10!D15:Week10!D19,Week11!D20,Week12!D20,Week13!D20,Week14!D20,Week15!D20,Week16!D20,Week17!D20,Week18!D20,Week19!D20,Week20!D20,Week21!D20,Week22!D20,Week23!D13,Week23!D14)*32

    Check Qtr Graph Cell D4

    to this:
    =SUM(Week10!D15:Week10!D19,Week11:Week22!D20,Week23!D13,Week23!D14)*Settings!D3

    note
    ek10!D15:Week10!D19 << specifies a cell range

    Week11:Week22!D20 << Specifies a Sheet range

    Week11:Week22!D20 Refers to the sheets where the Cell (D20) is the Cell we want to read in all sheets E.G the whole week is included in the quarter on the sheets between week11 and 22
    Go thru this link--

    Vane had helped me in this...

    http://www.excelforum.com/showthread.php?t=611112

  5. #5
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Quote Originally Posted by all4excel
    Go thru this link--

    Vane had helped me in this...

    http://www.excelforum.com/showthread.php?t=611112

    Thanks all4excel for posting the link.


    Quote Originally Posted by bindu
    Check Qtr Graph Cell D4

    to this:
    =SUM(Week10!D15:Week10!D19,Week11:Week22!D20,Week2 3!D13,Week23!D14)*Settings!D3

    note
    ek10!D15:Week10!D19 << specifies a cell range

    Week11:Week22!D20 << Specifies a Sheet range

    Week11:Week22!D20 Refers to the sheets where the Cell (D20) is the Cell we want to read in all sheets E.G the whole week is included in the quarter on the sheets between week11 and 22
    bindu,

    I'm little confuse what you're after you said;

    Quote Originally Posted by bindu
    Sum Cells D13 thru D19 per sheet depending if the quarter ends within a given week else just read Cell D 20:
    The formula I gave you did just that. Now to your second posting
    you want to sum cell D20 across multiple worksheets with a specific sheet range is this correct? if so click on the link that all4excel provided.

    But if the weeks don't fall in the specific quarter within the specific sheet range you'll get incorrect results.

    What are the results should be in D4 thru D6?

+ 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