count executive zero values within a columns of numbers

[tsejoe]

Hi There,

I have a data file in excel with the format below. I would like to know the Excel formula so that i can count the number of consecutive 0 to identify number of months no sales per item from the right. Any idea please?


Format:

Item Mth12 Mth11 Mth10 Mth9 Mth8 Mth7 Mth6 Mth5 Mth4 Mth3 Mth2 Mth1 Mth0 Expected Result
A 10 2 1 3 3 4 0 0 0 0 0 0 0 7
B 1 1 0 1 2 1 1 1 1 2 1 0 0 2
C 2 2 2 2 1 3 3 2 1 1 1 1 2 0

Thank you so much - much appreciated.


Best Regards,
Joe

 

[tsejoe]

Hi M,

I think i did not explain clear enough for your expert advice.....

item A 1 3 6 8 9 5 0 0 0 Expect result should be 3
item B 2 1 5 7 2 3 5 6 0 Expect result should be 1
item C 4 5 8 9 3 -1 2 -1 0 Expect result should be 2

Thank you so much~~~


Best Regards,
Joe

 

[Marcelo Branco]

Hi M,

I think i did not explain clear enough for your expert advice.....

item A 1 3 6 8 9 5 0 0 0 Expect result should be 3
item B 2 1 5 7 2 3 5 6 0 Expect result should be 1
item C 4 5 8 9 3 -1 2 -1 0 Expect result should be 2

Thank you so much~~~


Best Regards,
Joe

Maybe...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Item​

Result​

2​

A​

1​

3​

6​

8​

9​

5​

0​

0​

0​

3​

3​

B​

2​

1​

5​

7​

2​

3​

5​

6​

0​

1​

4​

C​

4​

5​

8​

9​

3​

-1​

2​

-1​

0​

2​

Formula in L2 copied down
=COUNTIF(INDEX($B2:$J2,LOOKUP(2,1/($B2:$J2>0),COLUMN($B2:$J2)-COLUMN($B2)+1)):$J2,"<=0")

M.

 

[tsejoe]

Hi M,

Yes! you are so right!! it now works in perfect condition....

Can you please kindly teach me about the logic of the formula please? Especially the index-lookup and column part.

Thank you so much~~


Best Regards,
Joe.

 

[Marcelo Branco]

INDEX(B2:J2,LOOKUP(2,....))
returns the reference of the last cell > 0 (rightmost in B2:J2 that is greater than zero) with the help of
LOOKUP(2,1/($B2:$J2>0),COLUMN($B2:$J2)-COLUMN($B2)+1)
that gets the position of the last cell >0 (greater than zero)

How LOOKUP works in this case?
See the the excellent explanation provided by Aladin Akyurek at
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

So the range used in COUNTIF begins with the last cell > 0 and ends in J2.
COUNTIF returns how many cells in this range are less or equal zero.

M.

 

[tsejoe]

Thank you so much M,

I will need to digest the logic....

Thanks very much once again - much appreciated~


Best Regards,
joe