How to sum 12 months of data, excluding months that have zero data?

oyeols

New Member
Joined
Aug 2, 2016
Messages
10
Hey chaps,

I have a table that shows monthly production volumes for circa 2 years up to Oct 2016. However, there are months where there is zero production because of shutdown etc. I need a formula that sums 12 months backward rolling production from Oct 16, ignoring the zeros. I.e, if you look at the table below Aug 15 to Oct 16 is 15 months, but because 3 months have zero production, its actually 12 months of production. The file has many of such rows, with the zero production months popping up in different months.

is there a simple formula that would work for all the rows and only add 12 months of production, ignoring the zeros?

Please help. Would be very grateful for any solution to this.



Aug-15Sep-15Oct-15Nov-15Dec-15Jan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16
84,890 91,260 90,560 80,969 - - 91,941 111,089 - 85,835 26,698 7,637 67,308 72,185 521

<colgroup><col span="15"></colgroup><tbody>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
Hey oyeols. Why does it have to ignore the zeros? If you using the SUM function and adding numbers, adding 0 is not going to change the result.
 
Upvote 0

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
I think what the OP wants is a sum of the last 12 non-blank months' values - is this correct?
 
Upvote 0

oyeols

New Member
Joined
Aug 2, 2016
Messages
10
I need to collect 12 months of actual data. The actual file has multiple rows with the zeros showing up in different months as per below (the actual file is still way bigger than that). The only way I know is to manually count 12 instances of actual production per row, but that would take too long.


Jan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15Oct-15Nov-15Dec-15Jan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16
150,805 150,705 138,145 176,652 147,084 82,215 141,791 113,288 101,812 198,262 96,645 - 20,793 103,229 158,466 182,709 126,193 48,723 23,331 125,908 75,451 14,047
686,928 522,079 343,292 467,842 490,295 472,591 353,019 127,286 269,577 619,009 248,230 - 84,701 284,481 459,788 501,184 481,506 127,264 70,455 375,975 362,411 30,872
262,831 218,586 234,066 289,642 166,913 9,947 86,139 127,895 122,760 264,347 130,768 - 125,046 206,276 149,058 64,028 59,395 16,267 11,202 64,485 85,393 15,166
118,922 73,540 91,282 88,873 24,667 - 28,213 54,592 49,512 76,348 23,943 - 16,472 24,730 23,361 28,543 26,798 8,204 6,993 17,035 15,889 2,517
129,466 87,778 113,198 102,793 79,384 - 55,349 96,932 88,299 84,137 21,893 - 21,010 - - 34,083 32,482 11,806 4,805 18,775 20,978 8,145
41,264 26,082 36,001 46,694 24,667 - 12,297 4,176 22,408 46,093 29,478 - 27,336 40,383 26,048 26,623 21,389 6,619 3,723 483 13,328 985
51,696 45,249 28,083 49,340 38,773 - 1,095 - 21,373 57,567 19,932 - 6,945 22,188 379 94 - - - 11,218 8,741 1,797
3,417 22,533 24,865 20,264 20,578 21,162 18,599 32,353 10,499 18,968 20,763 - - 9,229 26,947 29,359 - - - - - -
181,296 134,006 182,396 225,168 147,645 121,773 164,966 160,490 161,171 239,859 14,252 - 114,501 178,173 165,728 127,814 103,641 36,701 27,941 17,190 1,598 3,082
- - 35,282 37,718 27,318 - 23,923 27,058 28,466 25,270 18,938 - 18,106 30,095 1,675 35,044 18,644 - - - - -
137,835 49,881 47,893 70,182 - - - - - - - - - - - - - - - - - -
30,810 24,346 19,909 48,026 18,163 10,946 27,911 40,072 40,133 61,566 36,219 - 32,025 26,415 30,722 40,712 29,794 1,546 - - - 3,448
- - 16,763 - 54,806 - - - - - - - - - - - - - - - - -
42,768 29,670 12,210 24,299 9,692 387 11,736 3,933 19,707 39,059 29,924 - - 2,358 32,863 27,490 4,530 4,089 3,238 - - 4,516
30,255 24,870 28,540 26,370 22,907 9,790 16,985 18,853 22,177 23,456 16,394 - 1,536 - - - - - - - - -
159,127 102,714 116,768 96,959 86,904 57,322 40,329 84,890 91,260 90,560 80,969 - - 91,941 111,089 - 85,835 26,698 7,637 67,308 72,185 521
24,342 15,221 14,431 21,373 16,580 11,859 17,184 17,562 16,178 19,490 13,640 - 19,011 23,848 14,158 28,783 12,749 - - 2,890 18,855 1,359

<colgroup><col><col span="3"><col span="18"></colgroup><tbody>
</tbody>
 
Upvote 0

Robotico

Spammer
Joined
Dec 13, 2016
Messages
2
ADVERTISEMENT
Wow, interesting method I will be know about it.
 
Upvote 0

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
ADVERTISEMENT
Thanks for the clarification! Will have a think. Presumably the - means zero and is as a result of using accounting formatting - correct?
 
Upvote 0

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
How about something like this?


Excel 2010
ABCDEFGHIJKLMNOP
1Aug-15Sep-15Oct-15Nov-15Dec-15Jan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16Sum
284,89091,26090,56080,96991,941111,08985,83526,6987,63767,30872,185521810893
Sheet2
Cell Formulas
RangeFormula
P2{=SUM(O2:INDEX(A2:O2,LARGE(COLUMN(A2:O2)*(A2:O2<>""),12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Going off of your data in post #4, try this array formula in W2:

=SUM(V2:INDEX(A2:V2,LARGE(COLUMN(A2:V2)*(A2:V2<>"-"),12))) Ctrl Shift Enter
 
Upvote 0

Forum statistics

Threads
1,195,591
Messages
6,010,614
Members
441,558
Latest member
lambierules

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