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

 

[Aladin Akyurek]

O2, control+shift+enter, not just enter, and copy down

=MAX(FREQUENCY(IF(B2:N2=0,COLUMN(B2:N2)),IF(1-(B2:N2=0),COLUMN(B2:N2))))

 

[Marcelo Branco]

What is the expected result for..

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Item​

Mth12​

Mth11​

Mth10​

Mth9​

Mth8​

Mth7​

Mth6​

Mth5​

Mth4​

Mth3​

Mth2​

Mth1​

Mth0​

Expected Result​

2​

A​

2​

2​

0​

0​

0​

0​

4​

2​

1​

2​

2​

0​

0​

?​

<tbody>
</tbody>

M.

 

[tsejoe]

HI M,

it should be 2, expect the formula to always count from the right-hand-side towards the left, as soon as it find the next cell is greater than 0, it will be count up to the cell(s) to the right and stop.

Thanks very much

Best Regards,
Joe

 

[Aladin Akyurek]

HI M,

it should be 2, expect the formula to always count from the right-hand-side towards the left, as soon as it find the next cell is greater than 0, it will be count up to the cell(s) to the right and stop.

Thanks very much

Best Regards,
Joe

See post #2...

 

[tsejoe]

Hi,

tried the above formula, but it saids "#value!", any idea please?

Thanks very much

 

[Aladin Akyurek]

Hi,

tried the above formula, but it saids "#value!", any idea please?

Thanks very much

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[Marcelo Branco]

HI M,

it should be 2, expect the formula to always count from the right-hand-side towards the left, as soon as it find the next cell is greater than 0, it will be count up to the cell(s) to the right and stop.

Hi Joe

Maybe this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Item​

Mth12​

Mth11​

Mth10​

Mth9​

Mth8​

Mth7​

Mth6​

Mth5​

Mth4​

Mth3​

Mth2​

Mth1​

Mth0​

Expected Result​

2​

A​

10​

2​

1​

3​

3​

4​

0​

0​

0​

0​

0​

0​

0​

7​

3​

B​

1​

1​

0​

1​

2​

1​

1​

1​

1​

2​

1​

0​

0​

2​

4​

C​

2​

2​

2​

2​

1​

3​

3​

2​

1​

1​

1​

1​

2​

0​

5​

D​

2​

2​

0​

0​

0​

0​

4​

2​

1​

2​

2​

0​

0​

2​

Formula in O2 copied down
=COUNTIF(INDEX($B2:$N2,LOOKUP(2,1/($B2:$N2>0),COLUMN($B2:$N2)-COLUMN($B2)+1)):$N2,0)

Hope this helps

M.

 

[Aladin Akyurek]

Hi Joe
[...]

Formula in O2 copied down
=COUNTIF(INDEX($B2:$N2,LOOKUP(2,1/($B2:$N2>0),COLUMN($B2:$N2)-COLUMN($B2)+1)):$N2,0)

[...]

I think the FREQUENCY formula does what is asked for and it is faster.

 

[Marcelo Branco]

I think the FREQUENCY formula does what is asked for and it is faster.

Aladin,

Your formula returns 4 for the case i asked above (post #3)
I think OP doesnt want the MAX consecutive 0s.

M.