Count max consecutive number 0 in each column

[vitaalijs]

I use Excel 2013 and I want to count biggest number of appearances of number 0 in each column. There is more than 12000 rows in each column. Is it possible?

For example, if in column there is numbers 1 0 0 0 0 0 0 0 1 1 0 0 0 0 1
I want to get result 7


My file is here
https://www.dropbox.com/s/nlp87pz5inh229s/jautajumam.xlsx?dl=0

 

[Mel Smith]

Hi and welcome to the Board,

Try this:

=countif(range,0)

Mel

 

[Mel Smith]

I've just re-read your post, so I guess my solution isn't what you're after.

 

[vitaalijs]

Yes, my situation is more difficult. There is a lot of 0 and 1 in column, 0 is more than 1, and sometimes 0 repeats more than 15 times without having 1. And I want to know max number of these times when there is only 0 without 1.

 

[gaz_chops]

Try this entered as an array (Ctrl, Shift & Enter), adjust the ranges to suit your data.

=MAX(FREQUENCY(IF(A2:A17=0,ROW(A2:A17)),IF(A2:A17 <>0,ROW(A2:A17))))

 

[vitaalijs]

I had already found formula like this, but I cant understand, what I do incorrectly, because excel always shows me function error. Maybe I dont understand when I need to push this (Ctrl, Shift & Enter). I tried to do it before entering, after entering, but it doesnt work for me.
It shows that error is here - "...0,ROW..."

 

[gaz_chops]

Paste the formula into the formula bar, hold the Ctrl & Shift keys and press enter.

 

[vitaalijs]

I have already tried it but I dont know why it doesnt work for me and it shows an error here - "...0,ROW...". Maybe someone can open my file and try to do it for at least one column and then I can will check, what I was doing wrong?

 

[gaz_chops]

What version of excel are you using?

 

[vitaalijs]

Like I wrote in my first comment - Excel 2013.