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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi, I am also looking for a very similar code or formula. I am trying to analyse Rainfall Data and want to count the successive days with rainfall that were above 5 (mm) per day. Rainfall data in one month might look like this:

0
30
5
1
0
16
0
0
40
7
17
19
2
2
0
11
0
0
21
1
29
2

I know the result should be "4" [as we have 30,5; 40,7; 7,17; 17,19], but I do not manage to put the appropriate formula for this calculation together... Hopefully someone can help me out with this one. Thanks!
 
Upvote 0
Stefanovitz said:
Hi, I am also looking for a very similar code or formula. I am trying to analyse Rainfall Data and want to count the successive days with rainfall that were above 5 (mm) per day. Rainfall data in one month might look like this:

0
30
5
1
0
16
0
0
40
7
17
19
2
2
0
11
0
0
21
1
29
2

I know the result should be "4" [as we have 30,5; 40,7; 7,17; 17,19], but I do not manage to put the appropriate formula for this calculation together... Hopefully someone can help me out with this one. Thanks!
Click to expand...

Try...

=SUMPRODUCT(--(A2:A22>=5),--(A3:A23>=5))
 
Upvote 0
Stefanovitz said:
Thanks Aladin, but this only counts how many cells have values above "5", not consecutive cells...
Click to expand...

It does look at pairs of consecutive cells whether they are both >= 5.

Your output spec 30,5; 40,7; 7,17; 17,19 appear to mean what the formula does. Note A2:A22 vs A3:A23.
 
Upvote 0
Hello everyone,

I'm trying to do a similar thing. I would like to count the last number of consecutive cells containing the number "1". My data only has "1"s and blank cells.


ABCDEFGHIJ

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

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"]2[/TD]

</tbody>
Sheet4​
Thanks in advance!
 
Upvote 0
bkkhelper said:
Hello everyone,

I'm trying to do a similar thing. I would like to count the last number of consecutive cells containing the number "1". My data only has "1"s and blank cells.


A
B
C
D
E
F
G
H
I
J

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

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="bgcolor: #bfbfbf, align: right"]2
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="bgcolor: #bfbfbf, align: right"]1
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #bfbfbf, align: right"]1
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="bgcolor: #bfbfbf, align: right"]2
[/TD]

</TBODY>
Sheet4​
Thanks in advance!
Click to expand...

J2, control+shift+enter, just enter:
Rich (BB code): 
=LOOKUP(9.99999999999999E+307,FREQUENCY(IF(A2:I2=1,COLUMN(A2:I2)),
  IF(1-(A2:I2=1),COLUMN(A2:I2))))
 
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
649
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
593
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
933
epactheactor
E

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

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