# Count repeats of the Maximum number in each consecutive count

#### granzowd

##### New Member
Hi,
excel sample
In the example I have a column of only 3 words here / there / everywhere these run as results
Note: The column I have is large, and I need an easier method, then pen and paper.
These words “may” come in continuous repeated results.
Currently I count the "no appearance" of these continuous results.
So when it says “here”, then we know “there” and “everywhere” do not appear, and then they get a count of 1, the number increases as the text does not appear.
I now know is when a word does NOT appear (totally absent) for XX times.
Note: My count has determined the MAXimum amount of times that the text does NOT appear is 31 (Month), so a table starting at 31 and counting down

I would like to create a table that display how many times in the string this happens
So:
“here”
Not appeared 31 times in consecutive rows in the column XX times
Not appeared 30 times in consecutive rows in the column XX times
Not appeared 29 times in consecutive rows in the column XX times
Not appeared 28 times in consecutive rows in the column XX times
Not appeared 27 times in consecutive rows in the column XX times
Not appeared 26 times in consecutive rows in the column XX times
Not appeared 25 times in consecutive rows in the column XX times
Not appeared 24 times in consecutive rows in the column XX times
Not appeared 23 times in consecutive rows in the column XX times
Etc (all the way to 1)

And repeat in a table for the other 2 text words “there” and “everywhere” as well. (See Attached)

In my attached example currently I look in each column and write down the number highest number time a text is not present.

So if you look at “here” it counted a string of
3 has happenned 5 times and 4 has happenned 1 time and 12 has happenned 1 time

“there” 3 has happenned 2 times and 6 has happenned 2 times

“everywhere”
7 has happenned 2 times
8 has happenned 2 times
9 has happenned 1 time
18 has happenned 1 times

But as I explained in the total string of the 9 that appeared once in “everywhere” it has 7 and 8 in that string when counting up to 9
So I cant use a formula with an if exist or countif outcome.

Ideally instead of having my 3 columns counting just a formula that populates with a table appearance. Or I could hide those current counting columns too.

I normally work out most problems using this forum..but I am at a loss what the formula is called

David

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Is this, copied across and down, what you want?

granzowd 1.xlsm
1
2here
3here
4here
5everywhere
6here
7here
8there
9there
10there
11here
12there
13there
14there
15everywhere
16here
17here
18there
19here
20there
21there
22here
23everywhere
24here
25here
26there
27there
28there
29here
30there
31there
32everywhere
33here
34here
35there
36here
37there
38there
39here
40everywhere
41here
42here
43there
44there
45there
46here
47here
48here
49everywhere
50here
51here
52there
53there
54there
55here
56there
57there
58there
59there
60there
61there
62there
63there
64there
65there
66there
67there
68
69
70302928272625242322212019181716151413121110987654321
71here000000000000000000100000001526
72there000000000000000000000000202205
73everywhere000000000000100000000122000100
Sheet1
Cell Formulas
RangeFormula
B71:AE73B71=SUM(--(FREQUENCY(IF(\$A\$2:\$A\$67<>\$A71,ROW(\$A\$2:\$A\$67)),IF(\$A\$2:\$A\$67=\$A71,ROW(\$A\$2:\$A\$67)))=B\$70))

#### granzowd

##### New Member
FANTASTIC, I wasn't sure it was possible..
But you did it
Thanks heaps

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

Replies
2
Views
66
Replies
0
Views
61
Replies
14
Views
171
Replies
1
Views
165
Replies
9
Views
88