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

[oyeols]

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-15	Sep-15	Oct-15	Nov-15	Dec-15	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-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>

 

[godsaaint]

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

Since he is using the accounting format, I think it should be: =SUM(V2:INDEX(A2:V2,LARGE(COLUMN(A2:V2)*(A2:V2<>0),12)))

 

[63falcondude]

Since he is using the accounting format, I think it should be: =SUM(V2:INDEX(A2:V2,LARGE(COLUMN(A2:V2)*(A2:V2<>0),12)))

Good call if he is using accounting format like you said.

 

[AliGW]

He is - I asked earlier in the thread and he confirmed this.

 

[63falcondude]

He is - I asked earlier in the thread and he confirmed this.

Whoops... Missed that.

 

[oyeols]

Since he is using the accounting format, I think it should be: =SUM(V2:INDEX(A2:V2,LARGE(COLUMN(A2:V2)*(A2:V2<>0),12)))

Thanks. Cant seem to get it to work though. Gives me the "you've entered too few arguments for this function"

Not sure exactly where I got it wrong...

 

[AliGW]

Just copy and paste it and you should be good to go. Check for trailing spaces after it, however, before pressing CTRL+SHIFT+ENTER.

 

[oyeols]

It seems the formula only works with blanks? What if the blank cells are populated with zeros?

Putting zeros in the blank cells seems to change the outcome of the formula.

 

[godsaaint]

The formula I posted works with either blank cells(without formulas) or cells with zeroes. If you input zeroes into blank cells, it shouldn't change the outcome. If you are running into some kind of problem, provide an example please.

 

[63falcondude]

It seems the formula only works with blanks? What if the blank cells are populated with zeros?

Putting zeros in the blank cells seems to change the outcome of the formula.

You never said anything about blank cells and your example does not show any blank cells either. If a blank cell is populated with a zero then it is no longer a blank cell. That being said, the formula should still work since excel sees a blank cell as having a value of 0.

Again, the formula that you are using (assuming that your data is in columns A through V starting in row 2) is =SUM(V2:INDEX(A2:V2,LARGE(COLUMN(A2:V2)*(A2:V2<>0),12))) Ctrl Shift Enter