IF/AND Formula. Count consecutive

James8761

Board Regular
Joined
Apr 24, 2012
Messages
149
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

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
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Does your table end in column E?
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 29, 2016
Messages
537
Office Version
  1. 2016
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
Joined
Jun 12, 2014
Messages
68,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
52,345
Office Version
  1. 365
Platform
  1. Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,345
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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))
 
Solution

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
537
Office Version
  1. 2016
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
Joined
May 28, 2005
Messages
52,345
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
68,244
Office Version
  1. 365
Platform
  1. Windows
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))
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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