# Count max consecutive number 0 in each column

#### vitaalijs

##### New Member
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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Mel Smith

##### Well-known Member
Hi and welcome to the Board,

Try this:

=countif(range,0)

Mel

#### Mel Smith

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

#### vitaalijs

##### New Member
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

##### Well-known Member

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

##### New Member
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

##### Well-known Member

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

#### vitaalijs

##### New Member
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

##### Well-known Member
What version of excel are you using?

#### vitaalijs

##### New Member
Like I wrote in my first comment - Excel 2013.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,650
Messages
5,854,943
Members
431,687
Latest member
anthonyj

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back