Copying sum range

Luke Webb

New Member
Hi All

Possibly a very simple question, but has stumped me, so I hope someone can help.

In Sheet1 I have each day from 01/04/04 up to 31/03/05 (one day per row). I have a number of columns, each of which is filled with figures corresponding to all the days.

In Sheet2 I want the same columns headings, but instead of being split daily I want ithe sums of the weekly figures.. In my particular sheet, the first cell is going to be C2 and could simply be put as =SUM(Sheet1!B2:B8). C3 would then be =SUM(Sheet1!B9:B15), while D2 would be =SUM(Sheet1!C2:C8), and so on along and down.

The problem is that when I try to copy down =SUM(Sheet1!B2:B8) I get =SUM(Sheet1!B3:B9). Is there any formula that I can enter that I can put into C2 and then copy down and copy along to give me the right ranges?

I have looked through the message board for ages but can't find anybody else with quite the same problem.

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

ken2step

Well-known Member
You could insert a column in sheet1 to the right of your date and enter this formula in the top cell and fill down
=weeknum(A1,2) You can then hide this column if you like.

Then in sheet2 A1 enter this formula =weeknum(sheet1!A1,2)
Then in sheet2 A2 enter =A1+1 and fill this down 50 rows to get your full year.

Then in sheet2 C1 put this formula
=SUMPRODUCT(--(Sheet1!\$B1:\$B365=\$A1),Sheet1!C\$1:C\$365)
Then fill this to the right as many columns as you have in sheet1 and then down.
Of course you will have to adjust the last row in the ranges to match however far down your dates go.

Luke Webb

New Member
Thanks for replying - I'm afraid I can't get it to work; all it that comes out are zeros. Here's Sheet1 (with the Weeknum formula added)
Date Week Alpha Bravo Charlie Delta Echo Foxtrot Golf Hotel Lima
29/03/2004 14
30/03/2004 14
31/03/2004 14
01/04/2004 14 2660 12960
02/04/2004 14 6280
03/04/2004 14 2320 4080
04/04/2004 14
05/04/2004 15 2280
06/04/2004 15 2860
07/04/2004 15 3940
08/04/2004 15 2900
09/04/2004 15
10/04/2004 15
11/04/2004 15
12/04/2004 16
13/04/2004 16
14/04/2004 16
15/04/2004 16 2280
16/04/2004 16 3320 7380 3120
17/04/2004 16
18/04/2004 16
19/04/2004 17 3000 4280
20/04/2004 17
21/04/2004 17
22/04/2004 17 2280 2760
23/04/2004 17
24/04/2004 17 14720 3220
25/04/2004 17
26/04/2004 18 2340
27/04/2004 18 7380
28/04/2004 18 2220 3380
29/04/2004 18
30/04/2004 18 80 4150 13100 2960
01/05/2004 18
02/05/2004 18

(A1 has "Date" in it - the rows go down to 365).

I copied the formula exactly as described, but it returns zero. Apologies - am not the most talented of excel users...

Cheers

Luke

ken2step

Well-known Member
I actually edited my reply to note the extra column to add in sheet2 as well. re read my reply and try again please. It works for me.

ken2step

Well-known Member
I reworked the formula to what I think is your current setup so you can copy and paste them to your workbook. Note the sheet names have to be sheet1 and sheet2 though.

You could insert a column in sheet1 to the right of your date and enter this formula in the top cell and fill down
=weeknum(A1,2) You can then hide this column if you like.

Then in sheet2 A1 enter this formula =weeknum(sheet1!A1,2)
Then in sheet2 A2 enter =A1+1 and fill this down 50 rows to get your full year.

Then in sheet2 C1 put this formula
=SUMPRODUCT(--(Sheet1!\$B1:\$B365=\$A1),Sheet1!C\$1:C\$365)
Then fill this to the right as many columns as you have in sheet1 and then down.
Of course you will have to adjust the last row in the ranges to match however far down your dates go.

Luke Webb

New Member
Thanks Ken - I'll have a go with that.

Replies
10
Views
560
Replies
25
Views
509
Replies
5
Views
53
Replies
1
Views
30
Replies
2
Views
116

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,480
Messages
5,770,330
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back