Week number totals from sum of 7 day ranges in another sheet

[zoso]

Hi there!

This solution is particularly important for me, and I'd be even more grateful if this can be resolved, because of all the time it takes me to do this manually!

And if you're living anywhere near Bradford (UK!) I'll come over and buy you pint or 2 if you can sort it - seriously!!!

Here goes...

'Daily Tracking' worksheet contains the following:

Cells A2:A367 contain dates of the year (including Feb 29) in the format Jan 01, Jan 02, Jan 03...Jan 31...Dec 31.

Cells B1:CC1 contain years in the format 1981,1982, 1983...2061.

Cells B2:CC367 contain numbers that are entered manually.

'Weekly Tracking' worksheet contains the following:

Column A contains week numbers in the following sequence: A2 is week 1, A7 is week 2, A12 is week 3, A17 is week 4 and so on, to cell 257 which is week 52. Cell 259 is week 53 when used.

For example, taking A2 as an example row for Week 1 in 2005, cells B2:BZ2 contain the sum of 7 cells corresponding to the week number in 'Weekly Tracking' sheet. The formula I have at the moment is =IF(COUNTBLANK('Daily Tracking'!Y4:Y10)=7,"",SUM('Daily Tracking'!Y4:Y10))

Example 2, for Week 2 in 2005, A7 contains the next 7 days' cells in 'Daily Tracking' as follows: =IF(COUNTBLANK('Daily Tracking'!Y11:Y17)=7,"",SUM('Daily Tracking'!Y11:Y17))

Example 3, for week 52 of 2005, again contains the sum of the 7 days from this period in 'Daily Tracking' as follows: =IF(COUNTBLANK('Daily Tracking'!Y362:Y367)+('Daily Tracking'!Z2)=6,"",SUM('Daily Tracking'!Y362:Y367)+SUM('Daily Tracking'!Z2))

Note the first and third examples contain odd references i.e. the first one does not refer to Y2:Y8 but Y4:Y10, and the third example involves one cell from the next column - this is my problem!

Because week numbers don't correspond to months, every year I have to manually correct each range of 7 cells for the next year based on the change in cell range over the year end.

What I want to be able to do is avoid having to manually amend the above formula 52 or 53 times every year if Excel can automatically sum each of the 7 day ranges in the 'Daily Tracking' worksheet that the 'Weekly Tracking' week numbers refer to, automatically adjusted to whatever the current year happens to be.

I've tried to be as clear as possible with this, but please post back if there's anything you can't understand - I'd be so grateful if this can be solved!

TIA

 

[Krishnakumar]

Hi Zoso,

In 'Daily Tracking' worksheet insert a column before column a and formula in A2 and copied down,

=INT((B2-SUM(MOD(DATE(YEAR(B2-MOD(B2-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

iso date

In 'Weekly Tracking' worksheet also insert a column before column a and formula in A2 and copied down,

=IF(B2="",A1,--RIGHT(B2,LEN(B2)-5))

Now change your sum formula to;

=SUMIF('Daily Tracking'!$A$2:$A$359,A2,'Daily Tracking'!$Y$2:$Y$359)

HTH

 

[zoso]

Kris - thanks very much for your reply!

Unfortunately I am unable to insert columns where you suggested, as the sheets contains code which may not work if a column is inserted there.

I can, however, insert the column in Daily Tracking at CG, and Weekly Tracking to CC instead if it's possible to amend your formulas to take account of that?

Is there anybody else who can perhaps help me here please, either by amending the formulas or with an alternative solution?

Many thanks!

 

[Aladin Akyurek]

Kris - thanks very much for your reply!

Unfortunately I am unable to insert columns where you suggested, as the sheets contains code which may not work if a column is inserted there.

I can, however, insert the column in Daily Tracking at CG, and Weekly Tracking to CC instead if it's possible to amend your formulas to take account of that?

Is there anybody else who can perhaps help me here please, either by amending the formulas or with an alternative solution?

Many thanks!

It's a good tactic to produce a scaled-down sample (5 rows by 4 columns) along with expected results.

 

[zoso]

Hi Aladin - I have tried and tried to copy a few rows onto a new sheet and convert it to HTML to post using Kristy's HTML maker, but because I only started using the formulas in the last couple of years I can't get the formulas to relate to each other!

If there's anything that isn't clear I'm more than happy to explain it but it's just not possible for me to post the extracts like I'd hoped!

I've added another couple of example rows to try and highlight my problem further - I hope these help somebody?

As you've probably guessed, this really does mean a lot to me, and I'd be so grateful if a solution can be found...

 

[zoso]

I would be so grateful if a solution could be found for this - I'm happy to mail the workbook to whoever might be kind enough to take a look at it