# Count down from the Maximum number in each consecutive count

1. ## Count down from the Maximum number in each consecutive count

Hi,
I have attached a sample that should shelp the explanation.
In this example I have a column of only 3 words here / there / everywhere
The column is large, and I need an easier method.
These words “may” come in continuous strings.
Currently I count these continuous strings.
So when it says “here”, then we know “there” and “everywhere” do not appear.
What I want to 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 30

I would like to create a table that display how many times in the string this happens
So:
“here”
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 (to 1)

And repeat in a table for “there” and “everywhere” as well. (See Attached)

In my attached example currently I look in each column and write down the number
The number continues to count up as the text is absent.
Countif won’t work because the string ended at a count of 30 and 30 column has 29 28 27 26 etc.

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

2. ## Re: Count down from the Maximum number in each consecutive count

In Column B,C,D I can see formula, do they work?

3. ## Re: Count down from the Maximum number in each consecutive count

I am trying to get a result of how many times the total string number appears.

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

“there”
3 2 times
6 2 times

“everywhere”
7 2 times
8 2 times
9 1 time
18 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 outcome.

I cant get my head around how to just report the highest number in each string and how many times it has happened

4. ## Re: Count down from the Maximum number in each consecutive count

Hi,

In B71:

=COUNT(1/(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))

and copied down and to the right as required.

Regards