number of continuous 0s

[egit123]

Hello,

I have a large table (100x200) which is updated daily. Cells are either 0 or 1. For each column and starting from row 2 I need to count the number of continuos 0 until a cell with 1 is encountered (e.g. today cells from K2 to K13 are all 0, H14 is 1 so the result should be 12, however tomorrow K2 may get a 1 so tomorrow's result will have to be 0). I have tried with variuos combinations of match and index formulas but cannot get it right.

Many thanks for your help,

eg-it123

 

[AlphaFrog]

Try this...

=IFERROR(MATCH(1,K2:K13,0)-1,12)

 

[egit123]

Many thanks. It works!