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

ThomDubya

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

PeriodAmount
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​
-
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Are you want Sum based Year.
OR Each 12 Non-Zero Values
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1PeriodAmountPeriodTotal
201/01/1801145,759
302/01/1802141,676
403/01/180
504/01/180
605/01/180
706/01/180
807/01/180
908/01/180
1009/01/180
1110/01/180
1211/01/180
1312/01/180
1401/01/1914,595
1502/01/1912,562
1603/01/1915,809
1704/01/1914,550
1805/01/1915,228
1906/01/1911,812
2007/01/1916,388
2108/01/1914,384
2209/01/1911,733
2310/01/1914,615
2411/01/1912,681
2512/01/197,676
2601/01/2015,674
2702/01/2012,923
2803/01/200
2904/01/200
3005/01/2014,325
3106/01/2014,273
3207/01/2013,591
3308/01/2013,748
3409/01/2013,591
3510/01/208,712
3611/01/2010,028
3712/01/208,537
3801/01/210
3902/01/210
4003/01/210
4104/01/210
4205/01/210
4306/01/210
4407/01/210
4508/01/210
Sheet2
Cell Formulas
RangeFormula
D2:D3D2=SEQUENCE(COUNTIFS(B:B,"<>",B:B,"<>0")/12+1)
E2:E3E2=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.
 
Solution

ThomDubya

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Are you read my Post?
What is answer?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
NewRun.xlsm
ABCDE
1PeriodAmountPeriodTotal
21/1/2018020180
32/1/201802019162033
43/1/201802020125402
54/1/2018020210
65/1/201802022
76/1/201802023
87/1/20180
98/1/20180
109/1/20180
1110/1/20180
1211/1/20180
1312/1/20180
141/1/201914595
152/1/201912562
163/1/201915809
174/1/201914550
185/1/201915228
196/1/201911812
207/1/201916388
218/1/201914384
229/1/201911733
2310/1/201914615
2411/1/201912681
2512/1/20197676
261/1/202015674
272/1/202012923
283/1/20200
294/1/20200
305/1/202014325
316/1/202014273
327/1/202013591
338/1/202013748
349/1/202013591
3510/1/20208712
3611/1/202010028
3712/1/20208537
381/1/20210
392/1/20210
403/1/20210
414/1/20210
425/1/20210
436/1/20210
447/1/20210
458/1/20210
Sheet3
Cell Formulas
RangeFormula
E2:E5E2=SUMPRODUCT(($B$2:$B$200)*(YEAR($A$2:$A$200)=D2))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option
+Fluff v2.xlsm
ABCDE
1PeriodAmount
201/01/201801154106
301/02/2018013162033
401/03/20180
501/04/20180
601/05/20180
701/06/20180
801/07/20180
901/08/20180
1001/09/20180
1101/10/20180
1201/11/20180
1301/12/20180
1401/01/201914,595
1501/02/201912,562
1601/03/201915,809
1701/04/201914,550
1801/05/201915,228
1901/06/201911,812
2001/07/201916,388
2101/08/201914,384
2201/09/201911,733
2301/10/201914,615
2401/11/201912,681
2501/12/20197,676
2601/01/202015,674
2701/02/202012,923
2801/03/202014,011
2901/04/202014,693
3001/05/202014,325
3101/06/202014,273
3201/07/202013,591
3301/08/202013,748
3401/09/202013,591
3501/10/20208,712
3601/11/202010,028
3701/12/20208,537
3801/01/2021
3901/02/2021
4001/03/2021
4101/04/2021
4201/05/2021
4301/06/2021
4401/07/2021
4501/08/2021
Data
Cell Formulas
RangeFormula
D2:D3D2=SEQUENCE(ROUNDUP((COUNTIFS(B:B,">0"))/12,0),,,12)
E2:E3E2=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

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1PeriodAmountPeriodTotal
201/01/1801145,759
302/01/1802141,676
403/01/180
504/01/180
605/01/180
706/01/180
807/01/180
908/01/180
1009/01/180
1110/01/180
1211/01/180
1312/01/180
1401/01/1914,595
1502/01/1912,562
1603/01/1915,809
1704/01/1914,550
1805/01/1915,228
1906/01/1911,812
2007/01/1916,388
2108/01/1914,384
2209/01/1911,733
2310/01/1914,615
2411/01/1912,681
2512/01/197,676
2601/01/2015,674
2702/01/2012,923
2803/01/200
2904/01/200
3005/01/2014,325
3106/01/2014,273
3207/01/2013,591
3308/01/2013,748
3409/01/2013,591
3510/01/208,712
3611/01/2010,028
3712/01/208,537
3801/01/210
3902/01/210
4003/01/210
4104/01/210
4205/01/210
4306/01/210
4407/01/210
4508/01/210
Sheet2
Cell Formulas
RangeFormula
D2:D3D2=SEQUENCE(COUNTIFS(B:B,"<>",B:B,"<>0")/12+1)
E2:E3E2=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

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thanks everyone for the suggestions. I'll try the others later on
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top