Sum across 52 Sheets and Sum Cells D13 thru D19

[bindu]

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%

[vane0326]

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!

[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

[all4excel]

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

[vane0326]

Go thru this link--

Vane had helped me in this...

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

Thanks all4excel for posting the link.

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;

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?