0

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

newishuser
  • 610
  • 4
  • 24
Niclas
  • 1,069
  • 4
  • 18
  • 33
  • Have you considered `WEEKNUM`? – Tom Jun 05 '15 at 12:04
  • This may be of topical interest: [Translating results by day to results by week](http://stackoverflow.com/questions/30657990/translating-results-by-day-to-results-by-week-with-excel-vba/30658173#30658173). –  Jun 05 '15 at 12:06
  • I can see that it deleted my initial description. But yes @Tom I am using Weeknum in the bottom of the sheet, as you can see the 21 and 22 :) – Niclas Jun 05 '15 at 12:15
  • Where are the SUM's going on your sheet? Since you have WEEKNUM, you can just use SUMIF. – Ron Rosenfeld Jun 05 '15 at 12:16
  • @NiclasMadsen Ah I see. Can you explain what your rows contain (or what you're trying to sum exactly) and possibly explain your problem a bit more. – Tom Jun 05 '15 at 12:17
  • @Tom so all the rows contain data that we will use for reporting purpose. (Can´t really go into details with this, but I will only need the sum). @RonRosenfeld So behind all these datas, I will have columns saying week 21, week 22, etc. with the sum of each week going in to the respective cell. I can see that `SUMIF` is most likely the way to proceed, but I cannot get it to work smoothly. – Niclas Jun 05 '15 at 12:25
  • @NiclasMadsen Have you considered using a pivot table? You'd be able to order it by weeks then sum all the values you wanted. – Tom Jun 05 '15 at 12:29
  • @Tom indeed I have. But I don´t think it will do what you saying there. I have tried and I cannot get it to sum each row for each week. – Niclas Jun 05 '15 at 12:36
  • @NiclasMadsen Have you tried putting the Week Number in as a rows field then use a calculated field to combine the values that you want? (sorry I'm assuming you're fairly new to pivot table if not please ignore me) – Tom Jun 05 '15 at 12:37
  • @Tom That is very true. The thing is that the headers are vertical, so when I use my pivottable I get the top horizontal row. – Niclas Jun 05 '15 at 12:44
  • @NiclasMadsen Ah bugger, but why are you concerned about the headers (days) when you're reporting by week? Could you put up your picture as text instead so I could attempt to play around with it? – Tom Jun 05 '15 at 12:49
  • Okay that was just a mess.. Don´t know how to put in a table – Niclas Jun 05 '15 at 12:54
  • @NiclasMadsen Ok I'm with you now. I still think you should reconsider using a pivot table however to use one you would need to transpose your data set (so that you row headers are column headers instead). If you then did your whole report on the pivot table I think it would make your life a lot easier in the long run – Tom Jun 05 '15 at 13:17

2 Answers2

1

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:

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
1

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

Tom
  • 9,725
  • 3
  • 31
  • 48
  • Hi Tom, Thank you for your contribution. I gave you an upvote because of your explanation. I completely agree with your point in the long run, and I will raise it to "redesign" our reports. – Niclas Jun 07 '15 at 22:06