# Makro/Formula to count number of consecutive particular words in a list of random words

1. ## Makro/Formula to count number of consecutive particular words in a list of random words

Hi,

I'd appreciate any help on how to solve the following issue:
I have a binary set of words (lets say TRUE and FALSE) and want to count the number of consecutive "TRUE" words in a column.

E.g.

TRUE
TRUE
FALSE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE

And the macro or formula should tell me the following in a separate column:
2
1
3  Register To Reply

2. ## Re: Makro/Formula to count number of consecutive particular words in a list of random word

Hi,

Are those "words" text entries, or genuine Boolean TRUE/FALSE responses?

Regards  Register To Reply

3. ## Re: Makro/Formula to count number of consecutive particular words in a list of random word

Hi,

they are boolean true/false responses from a separate set of values. I wanted to keep it simple but I basically need the number of consecutive values that are below the mean of a column.

something like this:

7.7 TRUE
7.7 TRUE
7.7 TRUE
7.7 TRUE
10.78 FALSE
9.24 FALSE
10.78 FALSE
10.78 FALSE
9 TRUE
9.5 FALSE
7 TRUE

Result:
4
1
1

Best  Register To Reply

4. ## Re: Makro/Formula to count number of consecutive particular words in a list of random word

May as well perform the operation on the Booleans since you've already derived them.

Based on those Booleans being in B1:B10, first go to Name Manager (Formulas tab) and create the following:

Name: Arry1
Refers to: =FREQUENCY(IF(\$B\$1:\$B\$10,ROW(\$B\$1:\$B\$10)),IF(1-\$B\$1:\$B\$10,ROW(\$B\$1:\$B\$10)))

Exit Name Manager.

Then enter this array formula** in C1:

=SUM(IF(Arry1,1))

Then enter this array formula** in D1:

=IF(ROWS(\$1:1)>\$C\$1,"",INDEX(Arry1,SMALL(IFERROR(IF(Arry1,ROW(\$B\$1:\$B\$10)),""),ROWS(\$1:1))))

Copy this formula down (though not the one in C1) until you start to get blanks for the results.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).  Register To Reply

5. ## Re: Makro/Formula to count number of consecutive particular words in a list of random word

Works.

Thank you!  Register To Reply

6. ## Re: Makro/Formula to count number of consecutive particular words in a list of random word

You're welcome!  Register To Reply