IF/AND Formula. Count consecutive

James8761

Board Regular
Joined
Apr 24, 2012
Messages
154
Office Version
  1. 2019
Platform
  1. Windows
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 0ABCDE
012412
200121
101213
400002
010000
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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)))
 
Upvote 0
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)
 
Upvote 0
What about this?

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:
Upvote 0
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))
 
Upvote 0
Solution
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)
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top