Count number of appearances of a range of consecutive cells

P

Pedro's PhD

Board Regular
Joined
Jun 3, 2011
Messages
63
Hi,

Is there a way of counting the number of times a string of consecutive cells appear in a range?

Example:

How many times are there 5 consecutive cells with a value of below a set number referenced in B2 (say number 15), in the range A1 to A100.

Many thanks,

Pedro
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hello Pedro, assuming you would count a run of 7 values below B15, say, as a single instance (not three ranges of 5) then try this "array formula"

=SUM(IF(FREQUENCY(IF(A1:A100< B15,ROW(A1:A100)),IF(A1:A100 >B15,ROW(A1:A100)))>=5,1))

confirmed with CTRL+SHIFT+ENTER
 
Upvote 0
Pedro's PhD said:
Hi,

Is there a way of counting the number of times a string of consecutive cells appear in a range?

Example:

How many times are there 5 consecutive cells with a value of below a set number referenced in B2 (say number 15), in the range A1 to A100.

Many thanks,

Pedro
Click to expand...
Try this array formula**:

Assumes no empty cells within the data range.

<?XML:NAMESPACE PREFIX = B2,ROW(A1 /><B2,ROW(A1:A100)),IF(A1:A100>=SUM(IF(FREQUENCY(IF(A1:A100 < B2,ROW(A1:A100)),IF(A1:A100 > =B2,ROW(A1:A100)))=5,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
</B2,ROW(A1:A100)),IF(A1:A100>
 
Upvote 0
Hi,

Another question if possible....

Is there a way of returning the cell reference of the first cell of the first set of 5 consecutive cells?

Thanks.... Pedro
 
Upvote 0
Pedro's PhD said:
Hi,

Another question if possible....

Is there a way of returning the cell reference of the first cell of the first set of 5 consecutive cells?

Thanks.... Pedro
Click to expand...
I'm not sure how to do that using a single formula.

If the streak of 5 was the maximum longest streak then it can be done using a helper column.

Inspecting the formula, we can see where the first streak of 5 starts but it's not a linear relationship with the cells of the range. There's a bit of an "offset" and I can't figure out how to correct for the offset.
 
Upvote 0
You could use this "array formula" to give the relative row number in the range (which is the same as the actual row number if the range starts at row 1 as per the example)

=MATCH(5,COUNTIF(OFFSET(A1,ROW(A1:A96)-ROW(A1),,5),"<"&B15),0)

confirmed with CTRL+SHIFT+ENTER

Note that this applies to the range A1:A100 so the last 5 cell range starts at A96 hence A1:A96 in the formula.

If you want a cell reference like $A$75 then change to

=CELL("address",INDEX(A1:A100,MATCH(5,COUNTIF(OFFSET(A1,ROW(A1:A96)-ROW(A1),,5),"<"&B15),0)))
 
Upvote 0
barry houdini said:
You could use this "array formula" to give the relative row number in the range (which is the same as the actual row number if the range starts at row 1 as per the example)

=MATCH(5,COUNTIF(OFFSET(A1,ROW(A1:A96)-ROW(A1),,5),"<"&B15),0)

confirmed with CTRL+SHIFT+ENTER

Note that this applies to the range A1:A100 so the last 5 cell range starts at A96 hence A1:A96 in the formula.

If you want a cell reference like $A$75 then change to

=CELL("address",INDEX(A1:A100,MATCH(5,COUNTIF(OFFSET(A1,ROW(A1:A96)-ROW(A1),,5),"<"&B15),0)))
Click to expand...
That won't work if the first streak is longer than n.

That's one of the problems I was trying to account for.

Consider this data:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">100</TD></TR></TBODY></TABLE>


Looking for the 1st streak of 5 consecutives.

The streak is 9 but the MATCH formula returns 8. In this scenario there is no match.
 
Last edited:
Upvote 0
Hello Biff,

Yes, of course my suggested formula looks for the first run of 5 consecutive cells that match the criterion. I considered that the next cell was unimportant.......but if the requirement is to find the start point of the first set of exactly 5 cells (which isn't part of a larger set) then perhaps this version would suffice

=CELL("address",INDEX(A1:A100,SMALL(IF(A1:A100>=B15,ROW(A1:A100)),MATCH(5,FREQUENCY(IF(A1:A100 < B15,ROW(A1:A100)),IF(A1:A100 >=B15,ROW(A1:A100))),0)-1)+1))

confirmed with CTRL+SHIFT+ENTER
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Most Consecutive appearances in a range
2 3
Replies
27
Views
337
Fluff
Fluff
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
803
felixstraube
F
J
  • Question Question
Counting Number of Consecutive Negative/Positive Numbers in a Row
Replies
6
Views
462
Cubist
Cubist
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
696
felixstraube
F
L
  • Question Question
How to count the five consecutive 'x' value within the 10 horizontal cells
2
Replies
11
Views
414
bebo021999
B

Forum statistics

Threads
1,224,514
Messages
6,179,219
Members
452,895
Latest member
BILLING GUY

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