# IF/AND Formula. Count consecutive

#### James8761

##### Board Regular
Hi,
I am trying to count consecutive zero's in the table below. I'm guessing this would be an IF/AND formula, but I can't get it right. In the Consecutive 0 column I have manually input the correct answer.

The zero must start in Column A. So the 2 in Consecutive 0 Column adds up Column A and B, then stops as Coluumn C =1. Similarly, the bottom 0 in the Consecutive 0 Column - despite having 4 consecutive 0 from B-E - is zero as it has a number larger than 0 in Column A.

Any help greatly appreciated.

James

 CONSECUTIVE 0 A B C D E 0 1 2 4 1 2 2 0 0 1 2 1 1 0 1 2 1 3 4 0 0 0 0 2 0 1 0 0 0 0

#### Peter_SSs

##### MrExcel MVP, Moderator
.. or shorter (Might need Ctrl+Shift+Enter in 2019 version?)

21 09 09.xlsm
ABCDEFG
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
7500000
Count Consecutive (4)
Cell Formulas
RangeFormula
A2:A7A2=MATCH(1,--(B2:G2&""<>"0"),0)-1

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### James8761

##### Board Regular
Thanks so much guys. Very impressive!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
10
Views
211
Replies
2
Views
173
Replies
4
Views
73
Replies
26
Views
1K
Replies
1
Views
170

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,152,394
Messages
5,769,828
Members
425,574
Latest member
grimeslisa

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