# 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

Elaborating further Jook's answer:

Results for months 1 to 12 are located in columns A1:L1

We can use an array formula to find the desired result (Ctrl-Shift-Enter from a formula window to insert, curled brackets are inserted by Excel itself):

``````={COLUMNS(A1:L1)-LARGE(IF(A1:L1>0,COLUMN(A1:L1),"0"),1)}
``````

We store the column numbers for the cells where sales are greater than 0 and find the greatest column number (i.e. closest to the end of the reporting period). Then we need to distract the value from the overall number of columns.

-