Count number of cells in range since last number greater than zero

Hi.

I have a row of numbers that contain usages of a product on a rolling 12 month period. I want to count the number of cells (months) since the item was last used.

For example, I have the following row of data:

Product XYZ 2 2 2 2 5 0 1 1 0 2 0 0

The last column represents the previous month. So in this case, last month, there was no usage and the last time it was used was 3 months ago, because there was no usage last month or the month before.

What formula could be used to return a "3" as the result to show how many months ago as last used?

Appreciated!

Hi,

Try this -

=12-LOOKUP(1,0/(B1:M1<>0),COLUMN(B1:M1)-MIN(COLUMN(B1:M1)))

Assuming your Product name is in the cell A1 and months from B1 to M1

Cheers!

Hi.

Perhaps an array formula** is the simplest, assuming the numbers are in B1:M1:

Edit: misread question:

=COUNT(B1:M1)-MATCH(1,0/(B1:M1<>0))+1

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).