# 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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### RockEd

##### New Member
Does your table end in column E?

#### RockEd

##### New Member
If it does... then a simple formula would be like this:

=IF(AND(E1=0,D1=0,C1=0,B1=0,A1=0),5,IF(AND(D1=0,C1=0,B1=0,A1=0),4,IF(AND(C1=0,B1=0,A1=0),3,IF(AND(B1=0,A1=0),2,IF(A1=0,1,0)))))

There might be a different way of doing it though...

This is on the assumption the data starts in A1 through to E1- drag the formula down to display the result for the other rows.

#### Phuoc

##### Well-known Member
Book1
ABCDEF
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
Sheet1
Cell Formulas
RangeFormula
A2:A6A2=IF(B2<>0,0,IFERROR(MATCH(TRUE,INDEX(B2:F2<>0,0),0)-1,COLUMNS(B2:F2)))

#### Fluff

##### MrExcel MVP, Moderator

Another option
+Fluff 1.xlsm
ABCDEF
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
Master
Cell Formulas
RangeFormula
A2:A6A2=AGGREGATE(15,6,COLUMN(\$B\$1:\$F\$1)/(B2:F2>0),1)-COLUMN(B1)

#### Peter_SSs

##### MrExcel MVP, Moderator

21 09 09.xlsm
ABCDEF
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
Count Consecutive
Cell Formulas
RangeFormula
A2:A6A2=MATCH(1,--(B2:F2<>0),0)-1
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator

Or if it is possible that all columns are 0 then

21 09 09.xlsm
ABCDEF
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
7500000
Count Consecutive (2)
Cell Formulas
RangeFormula
A2:A7A2=IFNA(MATCH(1,--(B2:F2<>0),0)-1,COLUMNS(B2:F2))

#### Phuoc

##### Well-known Member
Book1
ABCDEF
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
Sheet1
Cell Formulas
RangeFormula
A2:A6A2=IF(B2=0,IF(C2=0,IF(D2=0,IF(E2=0,IF(F2=0,5,4),3),2),1),0)

#### Peter_SSs

##### MrExcel MVP, Moderator
Another (easily expandable) option

21 09 09.xlsm
ABCDEFG
1CONSECUTIVE 0ABCDE
2012412
3200121
4101213
5400002
6010000
7500000
Count Consecutive (3)
Cell Formulas
RangeFormula
A2:A7A2=COLUMNS(B2:G2)-LEN(--CONCAT(B2:F2,1))

#### Fluff

##### MrExcel MVP, Moderator
A variation on Peter's idea in post#9 in-case you can have numbers >9
Excel Formula:
``=COLUMNS(B2:G2)-LEN(--CONCAT(LEFT(B2:F2,1),1))``

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