Count max consecutive number 0 in each column

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
799
Office Version
  1. 365
Platform
  1. Windows
Hi and welcome to the Board,

Try this:

=countif(range,0)

Mel
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
799
Office Version
  1. 365
Platform
  1. Windows
I've just re-read your post, so I guess my solution isn't what you're after.
 

vitaalijs

New Member
Joined
Jun 21, 2015
Messages
27
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Jun 21, 2015
Messages
27
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

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

vitaalijs

New Member
Joined
Jun 21, 2015
Messages
27
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,845
Members
414,107
Latest member
Tigretto

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top