Counting Consecutive Cells with value

J

JulieO

New Member
Joined
Mar 4, 2009
Messages
14
I use Excel 2007 and I'm trying to find code or a formula that would count the number of consecutive cells in a row from left to right until it there is 0, Below is an example of what I'm trying to do:

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 28pt" span=5 width=37><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1280" width=36><COL style="WIDTH: 28pt" span=2 width=37><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=37>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>B1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>D1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>E1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=29>G1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 27pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=36>H1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>I1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>Result</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1232</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1231</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR></TBODY></TABLE>

Column J has the results.
 
Sort by date Sort by votes
I was copying the formula the wrong way, so now it works perfectly!!
As for the second part, I was trying to add a conditional to identify if the sequence is more than 5, but I'm stuck here, not sure if it helps.

=SUM(--((FREQUENCY(IF(A5:S5>=1,COLUMN(A5:S5)),IF(A5:S5<1,COLUMN(A5:S5))))>=5))
 
Upvote 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
Enrique Rod said:
I was copying the formula the wrong way, so now it works perfectly!!
Click to expand...
Good news! :)


Enrique Rod said:
As for the second part, I was trying to add a conditional to identify if the sequence is more than 5, but I'm stuck here, not sure if it helps.

=SUM(--((FREQUENCY(IF(A5:S5>=1,COLUMN(A5:S5)),IF(A5:S5<1,COLUMN(A5:S5))))>=5))
Click to expand...
That formula would count how many sequences 5 or longer but not how long those sequences are.

To record how long they are, try this copied across and down, assuming you have the TEXTJOIN function in your version of Excel.
Formula again confirmed with Ctrl+Shift+Enter before copying to the other cells.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
8First SequenceSecond SequenceThird SequenceX Sequence
91111111111111111112
1011111111111111156
Seq Count
 
Upvote 0
It worked, the only issue is that where there is a sequence that does not meet the criteria (less than 5 consecutive cells), it shows the "#VALUE!" error.
 
Upvote 0
Enrique Rod said:
It worked, the only issue is that where there is a sequence that does not meet the criteria (less than 5 consecutive cells), it shows the "#VALUE!" error.
Click to expand...
Sorry, after testing I forgot to reintroduce the IFERROR()
{=IFERROR(MID(SUBSTITUTE(TEXTJOIN(" ",1,IF(FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9)))>4,FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9))),""))," ",REPT(" ",100)),COLUMNS($U:U)*100-99,100)+0,"")}
 
Upvote 0
Peter_SSs said:
Sorry, after testing I forgot to reintroduce the IFERROR()
{=IFERROR(MID(SUBSTITUTE(TEXTJOIN(" ",1,IF(FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9)))>4,FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9))),""))," ",REPT(" ",100)),COLUMNS($U:U)*100-99,100)+0,"")}
Click to expand...


Thank you so much Peter!!
 
Upvote 0
MrKowz said:
Welcome to the forums!

Try:

Excel 2007
ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #d8d8d8"]A1[/TD]
[TD="bgcolor: #d8d8d8"]B1[/TD]
[TD="bgcolor: #d8d8d8"]C1[/TD]
[TD="bgcolor: #d8d8d8"]D1[/TD]
[TD="bgcolor: #d8d8d8"]E1[/TD]
[TD="bgcolor: #d8d8d8"]F1[/TD]
[TD="bgcolor: #d8d8d8"]G1[/TD]
[TD="bgcolor: #d8d8d8"]H1[/TD]
[TD="bgcolor: #d8d8d8"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]1232[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]1231[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=efefef]#efefef[/URL] , align: right"]7[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1

<tbody>
</tbody>


Array Formulas
Cell

<tbody>
[TH="align: left"]Formula[/TH]

[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=e0e0f0]#e0e0f0[/URL] "]I2[/TH]
[TD="align: left"]{=IFERROR( COLUMN(H2)-COLUMN(INDEX(A2:H2,,MATCH(9.99E+307,IF(A2:H2=0,1,"")))),0 )}

<tbody>

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself [/TD]

</tbody>
Click to expand...

Hi All! Have been reading through the solutions provided, you all are geniuses. Thanks a lot for giving your time to us, its much appreciated!!

I have a similar problem which would require a little bit of modification to the quoted formula. Best to my knowledge, I believe your expertise would help me here.

Brief:
- The above data is calculating results from the range A2:H2 for example
- However, my data is being dynamically populated everyday, therefore my range today, for example is A2:J2, tomorrow it will be A2:K2
- Therefore, I am looking for a range reference to the last filled cell in a range of cells
- Rest of the conditions stays the same, ie, counting the last consecutive entries till it hits a zero.

I'll be really thankful for a solution. Hopefull.
 
Upvote 0
Additional note:

Instead of 0s my condition to stop would be something else. This may drastically change the premise of the whole solution, but here it goes:

Suppose the Range A2:F2 has the following data today:
Yes No Yes Yes No Yes - The answer should be 1

Tomorrow if it is A2:G2:
Yes No Yes Yes No Yes Yes - The answer should be 2

Day after it is A2:H2:
Yes No Yes Yes No Yes Yes Yes - The answer should be 3

Day after that is A2:I2:
Yes No Yes Yes No Yes Yes Yes No - The answer should be 1

Day after that is A2:J2:
Yes No Yes Yes No Yes Yes Yes No No - The answer should be 2

Day after that is A2:K2:
Yes No Yes Yes No Yes Yes Yes No No Yes - The answer should be 1

Therefore, what is happening is, instead of 0s, it is taking into account the last value, seeing if it matches the previous value, if it is, rollup and count till a different value comes.

Additional note:
The formula would not be pasted in any cell in the range A2:N, it will be pasted in a cell for example, B3.
 
Upvote 0
Final Addition, please excuse me for multiple add ons :pray:

- Sum of Yes(s) should be in positives
- Sum of No(s) should be in negatives.

For example:
Day after it is A2:H2:
Yes No Yes Yes No Yes Yes Yes - The answer should be 3

Day after that is A2:J2:
Yes No Yes Yes No Yes Yes Yes No No - The answer should be -2
 
Upvote 0
Welcome to the MrExcel board!

Her are 2 options that I think do what you want. The formulas can be placed wherever you want. In my example, they are each copied down.

Excel Workbook
ABCDEFGHIJKL
2YesNoYesYesNoYes
3YesNoYesYesNoYesYes
4YesNoYesYesNoYesYesYes
5YesNoYesYesNoYesYesYesNo
6YesNoYesYesNoYesYesYesNoNo
7YesNoYesYesNoYesYesYesNoNoYes
8
911
1022
1133
12-1-1
13-2-2
1411
Count Last Seq
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
571
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
912
epactheactor
E

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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