Checking if a sequence of values is met

[Ride The Lightning]

Hi

I have a column of data with the values either True or False. I want to set up a formula that calculates if "True" appears 5 times in a sequence. I have tried the IF function and tried using it in an array, however, the formula doesn't account for the whole range.

Any pointers of what I could use would be much appreciated.

Cheers

RTL

 

[RoryA]

=ISNUMBER(MATCH(5,(FREQUENCY(IF(A1:A10,ROW(A1:A10)),IF(NOT(A1:A10),ROW(A1:A10)))),0))
should do it.

 

[Marcelo Branco]

This is ok for range A1:A100, assuming True and False as boolean (not text), and checks if there are exactly 5 ocurrences of True

=IF(ISNUMBER(MATCH(5,FREQUENCY(IF(A1:A100,ROW(A1:A100)),IF(NOT(A1:A100),ROW(A1:A100))),0)),"Yes","No")

Ctrl+Shift+Enter

M.

 

[Ride The Lightning]

Rorya, thanks, but it doesn't appear to work. Marcelo, thank you, seems to work perfectly.

 

[RoryA]

Did you array enter it with Ctrl+shift+enter?

 

[Ride The Lightning]

Apologies, no, I didn't. I amended it and it worked, many thanks.

 

[Marcelo Branco]

Marcelo, thank you, seems to work perfectly.

You are welcome and tks for the feedback

M.