I am looking for a formula to be able to return the total amount in each week number.

[JennyP]

EG: we produce something in week 2015 then we sell it in 2022. This product is on the ground for 7 weeks. The information I have is:

Production week Delivery week QTY
2015 2022 10
2015 2021 5
2016 2028 12
2018 2030 13
2019 2025 8

I need to be able to calculate the total qty that we have for each week. EG 2015 = 15, 2016 = 27.

 

[Toadstool]

Hi JennyP,

I can't see why 2016 would be 27 and not 12 but if that's a typo then does this work?

Book2
	A	B	C	D	E	F
1	Production week	Delivery week	QTY		Production week	Total
2	2015	2022	10		2015	15
3	2015	2021	5		2016	12
4	2016	2028	12		2018	13
5	2018	2030	13		2019	8
6	2019	2025	8			0
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MIN($A$2:$A$999)
F2:F6	F2	=SUMIFS($C$2:$C$999,$A$2:$A$999,E2)
E3:E6	E3	=IFERROR(INDEX($A$2:$A$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999>E2),1)),"")

 

[sandy666]

with Pivot Table

Production week	Sum of QTY
2015	15
2016	12
2018	13
2019	8

or Running Total with Power Query (Get&Transform)

Production week	Delivery week	QTY	Running Total
2015	2022	10	10
2015	2021	5	15
2016	2028	12	27
2018	2030	13	40
2019	2025	8	48

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Sum = Table.AddColumn(Index, "Running Total", each List.Sum(List.Range(Index[QTY],0,[Index]))),
    RC = Table.RemoveColumns(Sum,{"Index"})
in
    RC

 

[sandy666]

or like this

Production week	QTY	Running Total
2015	15	15
2016	12	27
2018	13	40
2019	8	48

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Production week"}, {{"QTY", each List.Sum([QTY]), type number}}),
    Index = Table.AddIndexColumn(Group, "Index", 1, 1),
    Sum = Table.AddColumn(Index, "Running Total", each List.Sum(List.Range(Index[QTY],0,[Index]))),
    RC = Table.RemoveColumns(Sum,{"Index"})
in
    RC

I overlooked this in the first post

2015 = 15, 2016 = 27.

 

[JennyP]

Hi JennyP,

I can't see why 2016 would be 27 and not 12 but if that's a typo then does this work?

Book2
	A	B	C	D	E	F
1	Production week	Delivery week	QTY		Production week	Total
2	2015	2022	10		2015	15
3	2015	2021	5		2016	12
4	2016	2028	12		2018	13
5	2018	2030	13[ Hi JennyP, I can't see why 2016 would be 27 and not 12 but if that's a typo then does this work? Book2 A B C D E F 1 Production week Delivery week QTY Production week Total 2 2015 2022 10 2015 15 3 2015 2021 5 2016 12 4 2016 2028 12 2018 13 5 2018 2030 13 2019 8 6 2019 2025 8   0 Sheet1 Cell Formulas Range Formula E2 E2 =MIN($A$2:$A$999) F2:F6 F2 =SUMIFS($C$2:$C$999,$A$2:$A$999,E2) E3:E6 E3 =IFERROR(INDEX($A$2:$A$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999>E2),1)),"") Hi JennyP, I can't see why 2016 would be 27 and not 12 but if that's a typo then does this work? Book2 A B C D E F 1 Production week Delivery week QTY Production week Total 2 2015 2022 10 2015 15 3 2015 2021 5 2016 12 4 2016 2028 12 2018 13 5 2018 2030 13 2019 8 6 2019 2025 8   0 Sheet1 Cell Formulas Range Formula E2 E2 =MIN($A$2:$A$999) F2:F6 F2 =SUMIFS($C$2:$C$999,$A$2:$A$999,E2) E3:E6 E3 =IFERROR(INDEX($A$2:$A$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999>E2),1)),"") Hi Toadstool, I probably have not explained the situation correctly.. so here goes.... we produce the product (qty of 5) in week 2020 then we sell the product in week 2025. This means that we have 5 on the ground in week 2020, 5 in wk 2021, 5 in wk 2022, 5 in week 2023, 5 in week 2024 then 0 in week 2025. we then produce 2 in week 2022 and sell it in 2024. the total on the ground each week would be 2020 x 5, 2021 x 5, 2022 x 7, 2023 x 7, 2024 x 5, 2025 x 0 I hope this explains it a little better. /XD][XD]	2019	8
6	2019	2025	8			0
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MIN($A$2:$A$999)
F2:F6	F2	=SUMIFS($C$2:$C$999,$A$2:$A$999,E2)
E3:E6	E3	=IFERROR(INDEX($A$2:$A$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999>E2),1)),"")

 

[JennyP]

Hi Toadstool, I probably have not explained the situation correctly.. so here goes.... we produce the product (qty of 5) in week 2020 then we sell the product in week 2025. This means that we have 5 on the ground in week 2020, 5 in wk 2021, 5 in wk 2022, 5 in week 2023, 5 in week 2024 then 0 in week 2025. we then produce 2 in week 2022 and sell it in 2024. the total on the ground each week would be 2020 x 5, 2021 x 5, 2022 x 7, 2023 x 7, 2024 x 5, 2025 x 0 I hope this explains it a little better.

 

[Toadstool]

Hi JennyP,

So your example doesn't have an example where a Delivery Date has yet passed. Maybe you could give a worked example in the format of a spreadsheet, or maybe this is it?

Book2
	A	B	C	D	E	F
1	Production week	Delivery week	QTY		Production week	Total
2	2015	2022	10		2015	15
3	2015	2021	5		2016	27
4	2016	2024	12		2018	40
5	2018	2026	13		2019	48
6	2019	2025	8		2021	47
7	2021	2023	4		2022	43
8	2022	2025	6		2023	39
9	2023	2026	0		2024	27
10	2024	2026	0		2025	13
11	2025	2026	0		2026	0
12	2026	2026	0		2027	0
13	2027	2026	0			0
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MIN($A$2:$A$999)
F2:F13	F2	=SUMIFS($C$2:$C$999,$A$2:$A$999,"<="&E2,$B$2:$B$999,">"&E2)
E3:E13	E3	=IFERROR(INDEX($A$2:$A$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999>E2),1)),"")