CODEWITHSUNDEEP
excelexcel-formulaweek-number
Niclas
Niclas

Reputation: 1252

Sum the values from the days in a specific week in Excel

So I have some rows of data and some columns with dates. As you can see on the image below.
I want the sum of the week for each row - but the tricky thing is that not every week is 5 days, so there might be weeks with 3 days. So somehow, I want to try to go for the weeknumber and then sum it.
Can anyone help with me a formular (or a VBA macro)?

I am completely lost after trying several approaches.

TABLE DATA 18-May-15 19-May-15 20-May-15 21-May-15 22-May-15 25-May-15 26-May-15 27-May-15 28-May-15 29-May-15 1-Jun-15 2-Jun-15 3-Jun-15 4-Jun-15 WEEK 1 TOTAL WEEK 2 TOTAL 33 15 10 19 18 8 10 15 10 29 16 24 8 26 74
18 11 8 17 0 6 16 9 16 16 36 9 6 4 55
0 0 1 0 0 1 0 0 1 0 0 3 3 2 8
30 7 4 8 8 11 10 3 0 11 3 4 5 6 18
0 0 0 11 0 0 0 1 0 7 8 1 1 2 12
1 1 4 0 5 1 6 2 1 4 2 4 5 4 15
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
52 27 22 36 23 15 32 26 27 49 54 37 19 34 144 30 50 25 21 34 12 33 32 26 43 54 43 18 32 147 0 0 1 0 3 0 0 0 0 0 0 0 0 0 0
29 5 3 4 4 1 1 2 4 4 3 4 2 3 12
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 4 1 10 9 0 0 0 0 0 1 1 2
1 2 0 0 0 0 0 1 3 0 0 0 2 2 4
15 29 5 17 16 4 18 20 12 28 25 22 4 23 74
11 15 11 3 15 7 11 9 5 12 18 10 5 7 40
1 0 2 1 1 0 0 1 8 1 4 3 2 0 9
3 6 7 0 2 1 4 2 1 2 7 8 7 2 24
21 21 21 21 21 22 22 22 22 22 23 23 23 23

Upvotes: 0

Views: 2831

Answers (2)

Tom
Tom

Reputation: 9878

I know you've already accepted an answer but just to show you:

If you transposed your data you would then be able to utilise the pivot tables

Raw Data

You could set up a calculated field to calculate exactly what you wanted (and depending on how you sorted/grouped the date you could sort this by weeks, months, quarters or even years

Calculated Field

You would then get all of your final values displayed in an easy to read format grouped by whatever you want. In my opinion this is a lot more powerful solution for the long run.

Result

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Using SUMIF is one way. But you need to get your references straight in order to make it easy to enter.

Note in the diagram below, the formula:

=SUMIF(Weeknums,M$1,$B2:$K2)

where weeknums is the row of calculated Week Numbers.

enter image description here

Also note that the column headers showing the Week number to be summed could be made more explanatory with custom formatting:

Upvotes: 1

Related Questions