Count number of appearances of a range of consecutive cells

[Pedro's PhD]

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

 

[T. Valko]

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

That returns a #NUM! error using this data:

Book1
	A
1	1
2	2
3	3
4	4
5	5
6	100
7	100
8	100
9	100
10	1
11	2
12	3
13	4
14	5
15	100
16	1
17	2
18	3
19	4
20	5
21	100
Sheet1

 

[barry houdini]

Good point!

For your example I assume that B15 should be >5 and < 100.......

I believe my suggestion works in all other situations (assuming continuous numbers from A1 - not necessarily as far as A100 but without gaps) except the one you've highlighted there TV, when the 1st "streak" starts at A1. This modification should deal with that

=CELL("address",INDEX(A1:A100,SMALL(IF({1,0},0,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)+ROWS(A1:A100)-1)+1))

 

[T. Valko]

Good point!

For your example I assume that B15 should be >5 and < 100.......

I believe my suggestion works in all other situations (assuming continuous numbers from A1 - not necessarily as far as A100 but without gaps) except the one you've highlighted there TV, when the 1st "streak" starts at A1. This modification should deal with that

=CELL("address",INDEX(A1:A100,SMALL(IF({1,0},0,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)+ROWS(A1:A100)-1)+1))

That worked well as long as the range of data started on row 1.

Instead of trying to figure out where to add the "offset correction" I simply indexed the entire column and that corrected the problem.

=CELL("address",INDEX(A:A,...

Good job!

 

[Pedro's PhD]

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

Hi Barry,

This was just what I wanted, perfect, thanks!

Pedro