Totaling cell values depending on week No.

If you could upload a example of what you have, what you want ( maybe on 2 separete sheets) , with explanations of how the values are arrived at, it would make it much easier to offfer a solution
(NOTE- Please remember to make the sample 'safe' - ie- no sensitive, personal, proprietary..etc data)

This will work :
=SUM(OFFSET($D$2,,,WEEKNUM(TODAY(),2)))

But if all the cells in D2-D53 are 0 until data is entered anyways:
=SUM($D$2:$D$53) is far more efficient (Excell doesn't have to calculate the offset or weeknum or today functions, the simple addition is very fast! )

Hope this helps

Edit-
Tought of a third solution:
=SUMIF($C$2:$C$53,"<="&WEEKNUM(TODAY(),2),$D$2:$D$53)

but again, the =SUM($D$2:$D$53) still seems more efficient in the end

Okay, now you have me completely confused.....

Oh dear...this isn't going too well is it?

Let me try 1 more time.

Each week 8 people Mr1 to 8, work an amount of hours. Usually 8 hrs each day. If a person, Mr1 works 12hrs on a day then he get 8 hrs normal and 4hrs banked. Mr1 now holds or shows 4hrs in the banked hrs column for week 1.

Week 2 Mr1 doesn't work any banked hrs so the sheet says zero for week 2.

As we work through the weeks Mr1 might of completed 16 hrs of banked over a period of 12 weeks, therefore, his total is 16.

Here's the problem, your probably thinking just total Mr1's cells and thats it - The problem is, the sheet is populated with holidays and adjust for months / weeks to come, all the way through the year Jan - Dec. which means Mr1 cells total amounts of hrs that have not been worked yet. This is why I only want to totals for the weeks completed.

This is the sort of thing I have at the minute for totalling all Mr1's cells.

=SUM(O4,O15,O26,O37,O48,O59,O70,O81,O92,O103,O114,O125,O136,O148,O159,O170,O181,O192,O203,O214,O225,O236,O247,O258,O269,O280,O291,O302,O313,O324,O335,O346,O357,O368,O379,O390,O401,O412,O423,O434,O445,O456,O467,O478,O489,O500,O511,O522,O533,O544,O555,O566,O577)/52

which then divides all by 52 weeks. So for each one of these cells, Mr1's hour sit for each week.

Hope this helps
Mike

Using your 2nd sample (yellow highlighted cells)
in H17:

Formula:

Please Login or Register  to view this content.

Drag down to H24
(NOTE 1: the ranges would have to be adapted to your "real" data)
(NOTE 2: I used the sunday week start option of weeknum() to return the current week, as that seems to be how your timesheets are set up)

Hope this helps

EDIT-
NOTE 3: I used the custom format of # to return blanks for cells that returned 0, you can change to general if you want the 0 to appear..

Dredwolf,

Let me give you the origanal sheet - I'll need to delete it later.

See if you can get the formula working?

The yellow column is the column I want to totalise

Thanks
Mike

Two more questions, Why is the Mark Hughes hours calculated differently than the others ? (and I am assuming that's the one you want the banked hours to calculate for( at least in the case of the sample)
And do you want a running total for each week, or just the weeks total in the banked hours cell?
(Note - one of the problems you may have been having is that some of your numbers are numbers, while others are text)

EDIT-
What I mean by calculated differently, is that everyone else is 8 hrs, Mark Hughes gets 7.5 (displays as 8, but the value is 7.5)

Okay Try this: Weekly notice board Pattern 2013.sol1.xlsm

Note - I've added another column for the cumulative total (M Hughes, doesn't seem to bank hours anyways )
I've also done the first four weeks
(The changes I've made are highlighted in Green's and Blues)
Week 1 uses a solution to deal with how your data IS, (okay solution)
Week 2 adjusts your hours calculation so that it all comes out as numbers, no text (better solution)
Week 3 gets rid of the mess of if's and uses VLOOKUP() function to return the hours from your Key table (way better solution)
Week 4 uses INDEX/MATCH for the same type of results as solution for week 3 (arguably, the best solution...worth arguing over a beer with anyways )

See if this is closer to what you are looking for

Hope this helps

You are very welcome
Glad we could finally get something that worked for you !

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

Dred