sum formula

[aarti_rto]

Hello Experts,

i have below data

Part Num	Date	Issue Qty
ABC	01-01-2024​	7962​
XYZ	02-01-2024​	2938​
ABC	03-01-2024​	430​
ABC	04-01-2024​	1832​
ABC	05-01-2024​	9413​
ABC	06-01-2024​	9759​
XYZ	07-01-2024​	8676​
XYZ	08-01-2024​	147​
XYZ	09-01-2024​	5097​
XYZ	10-01-2024​	9240​
XYZ	11-01-2024​	5261​
ABC	12-01-2024​	401​
XYZ	13-01-2024​	6336​
ABC	14-01-2024​	9302​
ABC	15-01-2024​	680​
ABC	16-01-2024​	2352​
XYZ	17-01-2024​	1650​
XYZ	18-01-2024​	182​

and need below output in another sheet

	Week Num	1​	2​	3​
Part Number
ABC		29396​	401​	12334​
Xyz		2938​	34757​	1832​

 

[CephasOz]

Hi @aarti_rto

A pivot table would quickly do the job, but there's a problem: how do you define a week? This is a common problem. Do you start your first week from the 1st of January? Or does it start on the Monday of the week which has most of the first 7 days in it? Or do you count weeks starting on Sundays? It would be helpful if you added another column titled "Week No" to your data and either calculated or entered what you think the week number should be. Then producing a pivot table from your data would be so easy. The "Week No" would go in the columns, the "Part Num" in the rows, and the "Issue Qty" would be the value to be summed.

 

[Peter_SSs]

Could you add a Week Num column to the data and then use a Pivot Table?

24 01 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Part Num	Date	Issue Qty	Week Num			Sum of Issue Qty	Week Num
2	ABC	1/01/2024	7962	1			Part Num	1	2	3	Grand Total
3	XYZ	2/01/2024	2938	1			ABC	29396	401	12334	42131
4	ABC	3/01/2024	430	1			XYZ	2938	34757	1832	39527
5	ABC	4/01/2024	1832	1			Grand Total	32334	35158	14166	81658
6	ABC	5/01/2024	9413	1
7	ABC	6/01/2024	9759	1
8	XYZ	7/01/2024	8676	2
9	XYZ	8/01/2024	147	2
10	XYZ	9/01/2024	5097	2
11	XYZ	10/01/2024	9240	2
12	XYZ	11/01/2024	5261	2
13	ABC	12/01/2024	401	2
14	XYZ	13/01/2024	6336	2
15	ABC	14/01/2024	9302	3
16	ABC	15/01/2024	680	3
17	ABC	16/01/2024	2352	3
18	XYZ	17/01/2024	1650	3
19	XYZ	18/01/2024	182	3
PT by week

Cell Formulas
Range	Formula
D2:D19	D2	=WEEKNUM(B2)

 

[aarti_rto]

hello,

thank to reply but , i can not add Weeknum Formula , my data is huge
and i need this summary to xtract in different workbook which has other formulas and data also

 

[Peter_SSs]

, i can not add Weeknum Formula , my data is huge

Could be all the more reason to use a helper column to keep other formulas simpler & less burdensome on resources?

i need this summary to xtract in different workbook

need below output in another sheet

Is it in another workbook or another worksheet in the same workbook?


If the data is huge any formula solution is likely to make your formula calculations for this sluggish. Would a vba solution be an option?

BTW, you did not address the other questions asked by @CephasOz