Sum a Specified Number of Non-Zero Values in a List

[ThomDubya]

Hi All. First time posting but I have referenced this forum many times over the years. It has been a tremendous help. So thank you!

I am trying to get a formula that I can sum non-zero values in a range, but only the first 12 (1-12) from the most recent dates, and then the next 12 (13-24). I found something that works for a range across a row, but I can't figure out how to apply that same concept to a range in a column. My data in column A is months from Jan 2018 - August 2021 (A2:A45). Column B is amounts (B2:B45). The amounts in column B will vary...sometimes having nonzero values in certain months and sometimes it will be zero. Really what I am trying to do is get a sum of the most recent 12 months and then the next 12 months. Appreciate any help.

Period	Amount
1/1/18​	-
2/1/18​	-
3/1/18​	-
4/1/18​	-
5/1/18​	-
6/1/18​	-
7/1/18​	-
8/1/18​	-
9/1/18​	-
10/1/18​	-
11/1/18​	-
12/1/18​	-
1/1/19​	14,595
2/1/19​	12,562
3/1/19​	15,809
4/1/19​	14,550
5/1/19​	15,228
6/1/19​	11,812
7/1/19​	16,388
8/1/19​	14,384
9/1/19​	11,733
10/1/19​	14,615
11/1/19​	12,681
12/1/19​	7,676
1/1/20​	15,674
2/1/20​	12,923
3/1/20​	14,011
4/1/20​	14,693
5/1/20​	14,325
6/1/20​	14,273
7/1/20​	13,591
8/1/20​	13,748
9/1/20​	13,591
10/1/20​	8,712
11/1/20​	10,028
12/1/20​	8,537
1/1/21​	-
2/1/21​	-
3/1/21​	-
4/1/21​	-
5/1/21​	-
6/1/21​	-
7/1/21​	-
8/1/21​	-

 

[maabadi]

Welcome to MrExcel Message Board.
Are you want Sum based Year.
OR Each 12 Non-Zero Values

 

[jasonb75]

I'll have a look to see if I can make the formulas a bit more efficient but want to be sure that I understand correctly what is required.
The cell colours show the amounts that make up each total (note that I've purposely changed 2 values in the middle to zero so it is not an exact match for your sample).

Book1
	A	B	C	D	E
1	Period	Amount		Period	Total
2	01/01/18	0		1	145,759
3	02/01/18	0		2	141,676
4	03/01/18	0
5	04/01/18	0
6	05/01/18	0
7	06/01/18	0
8	07/01/18	0
9	08/01/18	0
10	09/01/18	0
11	10/01/18	0
12	11/01/18	0
13	12/01/18	0
14	01/01/19	14,595
15	02/01/19	12,562
16	03/01/19	15,809
17	04/01/19	14,550
18	05/01/19	15,228
19	06/01/19	11,812
20	07/01/19	16,388
21	08/01/19	14,384
22	09/01/19	11,733
23	10/01/19	14,615
24	11/01/19	12,681
25	12/01/19	7,676
26	01/01/20	15,674
27	02/01/20	12,923
28	03/01/20	0
29	04/01/20	0
30	05/01/20	14,325
31	06/01/20	14,273
32	07/01/20	13,591
33	08/01/20	13,748
34	09/01/20	13,591
35	10/01/20	8,712
36	11/01/20	10,028
37	12/01/20	8,537
38	01/01/21	0
39	02/01/21	0
40	03/01/21	0
41	04/01/21	0
42	05/01/21	0
43	06/01/21	0
44	07/01/21	0
45	08/01/21	0
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=SEQUENCE(COUNTIFS(B:B,"<>",B:B,"<>0")/12+1)
E2:E3	E2	=SUMPRODUCT($B$2:$B$45*IFERROR(($A$2:$A$45=TRANSPOSE(AGGREGATE(14,6,$A$2:$A$45/SIGN($B$2:$B$45),($D2-1)*12+ROW($1:$12)))),0))
Dynamic array formulas.

 

[ThomDubya]

Welcome to MrExcel Message Board.
Are you want Sum based Year.
OR Each 12 Non-Zero Values

So in the example above, I want it to add the values 8537, 10028......15674 (most recent 12 months of available values). Then add the next 12 months which would be values 7676, 12681....14595. Of course as that data in column B changes (say we get a value for 1/1/21) I want it to automatically adjust for the most recent 12 months. I hope that makes sense. Thanks!

 

[maabadi]

Are you read my Post?
What is answer?

 

[maabadi]

Try this:

NewRun.xlsm
	A	B	C	D	E
1	Period	Amount		Period	Total
2	1/1/2018	0		2018	0
3	2/1/2018	0		2019	162033
4	3/1/2018	0		2020	125402
5	4/1/2018	0		2021	0
6	5/1/2018	0		2022
7	6/1/2018	0		2023
8	7/1/2018	0
9	8/1/2018	0
10	9/1/2018	0
11	10/1/2018	0
12	11/1/2018	0
13	12/1/2018	0
14	1/1/2019	14595
15	2/1/2019	12562
16	3/1/2019	15809
17	4/1/2019	14550
18	5/1/2019	15228
19	6/1/2019	11812
20	7/1/2019	16388
21	8/1/2019	14384
22	9/1/2019	11733
23	10/1/2019	14615
24	11/1/2019	12681
25	12/1/2019	7676
26	1/1/2020	15674
27	2/1/2020	12923
28	3/1/2020	0
29	4/1/2020	0
30	5/1/2020	14325
31	6/1/2020	14273
32	7/1/2020	13591
33	8/1/2020	13748
34	9/1/2020	13591
35	10/1/2020	8712
36	11/1/2020	10028
37	12/1/2020	8537
38	1/1/2021	0
39	2/1/2021	0
40	3/1/2021	0
41	4/1/2021	0
42	5/1/2021	0
43	6/1/2021	0
44	7/1/2021	0
45	8/1/2021	0
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=SUMPRODUCT(($B$2:$B$200)*(YEAR($A$2:$A$200)=D2))

 

[Fluff]

Another option

+Fluff v2.xlsm
	A	B	C	D	E
1	Period	Amount
2	01/01/2018	0		1	154106
3	01/02/2018	0		13	162033
4	01/03/2018	0
5	01/04/2018	0
6	01/05/2018	0
7	01/06/2018	0
8	01/07/2018	0
9	01/08/2018	0
10	01/09/2018	0
11	01/10/2018	0
12	01/11/2018	0
13	01/12/2018	0
14	01/01/2019	14,595
15	01/02/2019	12,562
16	01/03/2019	15,809
17	01/04/2019	14,550
18	01/05/2019	15,228
19	01/06/2019	11,812
20	01/07/2019	16,388
21	01/08/2019	14,384
22	01/09/2019	11,733
23	01/10/2019	14,615
24	01/11/2019	12,681
25	01/12/2019	7,676
26	01/01/2020	15,674
27	01/02/2020	12,923
28	01/03/2020	14,011
29	01/04/2020	14,693
30	01/05/2020	14,325
31	01/06/2020	14,273
32	01/07/2020	13,591
33	01/08/2020	13,748
34	01/09/2020	13,591
35	01/10/2020	8,712
36	01/11/2020	10,028
37	01/12/2020	8,537
38	01/01/2021
39	01/02/2021
40	01/03/2021
41	01/04/2021
42	01/05/2021
43	01/06/2021
44	01/07/2021
45	01/08/2021
Data

Cell Formulas
Range	Formula
D2:D3	D2	=SEQUENCE(ROUNDUP((COUNTIFS(B:B,">0"))/12,0),,,12)
E2:E3	E2	=SUM(INDEX(SORT(FILTER($A$2:$B$45,$B$2:$B$45>0),1,-1),SEQUENCE(MIN(COUNTIFS(B:B,">0")-(D2-1),12),,D2),2))
Dynamic array formulas.

 

[ThomDubya]

I'll have a look to see if I can make the formulas a bit more efficient but want to be sure that I understand correctly what is required.
The cell colours show the amounts that make up each total (note that I've purposely changed 2 values in the middle to zero so it is not an exact match for your sample).

Book1
	A	B	C	D	E
1	Period	Amount		Period	Total
2	01/01/18	0		1	145,759
3	02/01/18	0		2	141,676
4	03/01/18	0
5	04/01/18	0
6	05/01/18	0
7	06/01/18	0
8	07/01/18	0
9	08/01/18	0
10	09/01/18	0
11	10/01/18	0
12	11/01/18	0
13	12/01/18	0
14	01/01/19	14,595
15	02/01/19	12,562
16	03/01/19	15,809
17	04/01/19	14,550
18	05/01/19	15,228
19	06/01/19	11,812
20	07/01/19	16,388
21	08/01/19	14,384
22	09/01/19	11,733
23	10/01/19	14,615
24	11/01/19	12,681
25	12/01/19	7,676
26	01/01/20	15,674
27	02/01/20	12,923
28	03/01/20	0
29	04/01/20	0
30	05/01/20	14,325
31	06/01/20	14,273
32	07/01/20	13,591
33	08/01/20	13,748
34	09/01/20	13,591
35	10/01/20	8,712
36	11/01/20	10,028
37	12/01/20	8,537
38	01/01/21	0
39	02/01/21	0
40	03/01/21	0
41	04/01/21	0
42	05/01/21	0
43	06/01/21	0
44	07/01/21	0
45	08/01/21	0
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=SEQUENCE(COUNTIFS(B:B,"<>",B:B,"<>0")/12+1)
E2:E3	E2	=SUMPRODUCT($B$2:$B$45*IFERROR(($A$2:$A$45=TRANSPOSE(AGGREGATE(14,6,$A$2:$A$45/SIGN($B$2:$B$45),($D2-1)*12+ROW($1:$12)))),0))
Dynamic array formulas.

This worked great. Thank you so much!

 

[ThomDubya]

Thanks everyone for the suggestions. I'll try the others later on