# Excel formula, counting number of cells since last non zero

I have a number of columns indicating sales per month for example the following might indicate that two months ago there was a sale from this customer of \$3555.

0,0,0,0,0,0,0,0,0,3555,0,0

I need a formula to calculate the number of months since the last sale, i.e. the result in this case should be 2.

-

Here's an quick solution for this. However, you need a helper row, which means, there is room for improvement. Maybe someone else can take it from here.

This is the setup:

month 1 2 3 4 5 6 7 8 9 10 11 12
sale  0 0 0 0 5 0 0 0 8 11 0  0
help  0 0 0 0 5 0 0 0 9 10 0  0

help-formula:

IF(SALE>0,MONTH,0) - IF(A2>0,A1,0)

months since last sale:

MAX(A1:L1)-MATCH(MAX(A3:L3);A3:L3;0)

The main problem is to get to the max column number with a sale value greater than 0. Right now, I can only think of VBA & Matrix-Function to solve this without the helper-row. But I have the feeling I am missing something.

-
Thanks for the idea in the last paragraph. – Jüri Ruut Nov 10 '12 at 21:04