Count number of last 12 non-zero months of sales (consecutive values) in a row

[evesin]

I have a spreadsheet that I'm trying to figure out a formula for, but I'm stuck. My data needs to be calculated across rows. I'm trying to calculate the last 12 (or more) months in consecutive order, when sales were bigger than 0 on each row. The catch is that I need to count those values from the end of the row before a 0 sales value occurs.
The columns all represent months of the year.

I have found a formula in the forums, which gives me the result of max consecutive values greater than 0 in a row, but it does not include the condition of LAST 12 or more months of non-zero sales, beginning to count from the right side of the row until the first month of 0 sales occur. The formula is the following:
=MAX(FREQUENCY(IF(D10:AP10>0;COLUMN(D10:AP10));IF(D10:AP10<=0;COLUMN(D10:AP10))))

For example > the formula applied for the marked row returns a count of 24 (counting the values where sales were bigger than 0), though the needed result should be 0, because counting from the last month of sales (from the right side) - there were no sales.

Thank You!

 

[Domenic]

The following formula will find the position of the first cell within the row that contains a zero value, starting from the right side, and then subtracts 1 from that position to return the number of consecutive non-zero values.

Note the following . . .

1. If it does not find a cell that contains a zero value, it simply returns a count of the number of cells referenced in the range.
2. If a cell is empty, it considers the cell as containing a zero value.
3. If a cell is blank, such as a cell that contains a formula that returns a blank/null string, the formula will need to be amended.

Therefore, assuming that you want to return the results in Column AR, enter the following formula in AR10, confirm with CONTROL+SHIFT+ENTER, and copy down:

=IFERROR(MATCH(TRUE,INDEX(D10:AP10,N(IF(1,COLUMNS(D10:AP10)-COLUMN(D10:AP10)+COLUMN(D10))))=0,0)-1,COLUMNS(D10:AP10))

Hope this helps!

 

[evesin]

Thank You, Domenic!

The formula provided worked like charm!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It looks like you don't have any empty cells so I have not allowed for number 2. of @Domenic's post.
However, if you have recent Excel version (ref my first paragraph above) with the XMATCH function then you could also try this (normal entry)

=COLUMNS(D10:AP10)-IFNA(XMATCH(0,D10:AP10,0,-1),0)

 

[evesin]

Thank You for the information @Peter_SSs , the Account Details are updated.

 

[Peter_SSs]

Account Details are updated.

Thanks for that.

In that case you should be able to use that much simpler formula?

 

[evesin]

Yes, the shorter formula works fine. Thank You!