Calculate qty produced each week from daily data

[PHolt]

Hi,

I have 2 worksheets one daily and 2nd for weekly. I look for the week number using WEEKNUM

Daily sheet look like this
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Date[/TD]
[TD]Week Number[/TD]
[TD]Time Taken[/TD]
[TD]Qty Completed[/TD]
[TD]Row No[/TD]
[/TR]
[TR]
[TD]26/02/2013[/TD]
[TD]9[/TD]
[TD]65.00[/TD]
[TD]25[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]26/02/2013[/TD]
[TD]9[/TD]
[TD]435.00[/TD]
[TD]183[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]27/02/2013[/TD]
[TD]9[/TD]
[TD]310.00[/TD]
[TD]500[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]27/02/2013[/TD]
[TD]9[/TD]
[TD]18.00[/TD]
[TD]30[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]28/02/2013[/TD]
[TD]9[/TD]
[TD]165.00[/TD]
[TD]36[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]04/03/2013[/TD]
[TD]10[/TD]
[TD]30.00[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]04/03/2013[/TD]
[TD]10[/TD]
[TD]100.00[/TD]
[TD]62[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]05/03/2013[/TD]
[TD]10 weeknum(A9,1)[/TD]
[TD]258.00[/TD]
[TD]124[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]05/03/2013[/TD]
[TD]10[/TD]
[TD]147.00[/TD]
[TD]169[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]05/03/2013[/TD]
[TD]10[/TD]
[TD]159.00[/TD]
[TD]87[/TD]
[TD]11[/TD]
[/TR]
</TBODY>[/TABLE]

Weekly Sheet

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Week Commencing[/TD]
[TD]Week Number[/TD]
[TD]Total time taken[/TD]
[TD]total qty completed[/TD]
[/TR]
[TR]
[TD]25/02/2013[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/03/2013[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/03/2013[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I have tried using a sumif formula, if week numbers match, total the time taken. But this isn't working. For example for calculating week 9 it would also include the data from row 9 because there is a 9 in the weeknum formula.

I think I may have to use SUMPRODUCT, but have never used this before. can some help please?

Thanks
Pam

 

[Comfy]

Sumif should work:

Excel 2010
	A	B	C	D	E
1	Date	Week Number	Time Taken	Qty Completed	Row No
2	26/02/13	9	65	25	2
3	26/02/13	9	435	183	3
4	27/02/13	9	310	500	4
5	27/02/13	9	18	30	5
6	28/02/13	9	165	36	6
7	04/03/13	10	30	3	7
8	04/03/13	10	100	62	8
9	05/03/13	10	258	124	9
10	05/03/13	10	147	169	10
11	05/03/13	10	159	87	11
12
13
14
15
16
17
18
19	Week Commencing	Week Number	Total time taken	total qty completed
20	25/02/13	9	993	774
21	01/03/13	10	694	445
22	08/03/13	11	0	0
Sheet1

Cell Formulas
Range	Formula
B2	=WEEKNUM(A2,1)
B3	=WEEKNUM(A3,1)
B4	=WEEKNUM(A4,1)
B5	=WEEKNUM(A5,1)
B6	=WEEKNUM(A6,1)
B7	=WEEKNUM(A7,1)
B8	=WEEKNUM(A8,1)
B9	=WEEKNUM(A9,1)
B10	=WEEKNUM(A10,1)
B11	=WEEKNUM(A11,1)
C20	=SUMIF($B$2:$B$11,B20,$C$2:$C$11)
C21	=SUMIF($B$2:$B$11,B21,$C$2:$C$11)
C22	=SUMIF($B$2:$B$11,B22,$C$2:$C$11)
D20	=SUMIF($B$2:$B$11,B20,$D$2:$D$11)
D21	=SUMIF($B$2:$B$11,B21,$D$2:$D$11)
D22	=SUMIF($B$2:$B$11,B22,$D$2:$D$11)

 

[PHolt]

Thanks, no sure what Id id wrong then