Count where a sequence of 3 trues occur

[GorD]

Based on this data, how would i find how many groups of 3 consecutive ones I have - so in this example the answer would be 1.

The data is in a single row 28 cells

1 0 0 0 0 0 0 1 0 0 0 0 0 1 1 0 0 0 0 1 1 1 0 0 0 0 1 1

 

[Domenic]

Domenic,

Beautiful formula!!!

I was wondering if when we have four consecutives "1" if we should count this as two sequences of 3 consecutives "1".
1 1 1 1
1 1 1 1

If we have 5, three sequences. 6, 4 sequences, and so on.

In this case, i think, your formula could be adjusted to

=SUM(IF(FREQUENCY(IF(A2:AB2=1,COLUMN(A2:AB2)),IF
(A2:AB2<>1,COLUMN(A2:AB2)))>=3,FREQUENCY(IF(A2:AB2=1,COLUMN(A2:AB2)),IF(A2:AB2<>1,COLUMN(A2:AB2)))-2))
Ctrl+Shift+Enter

Of course this depends on the actual user need. Just speculating ...

M.

Unclear... Can you elaborate and provide a more comprehensive example?

 

[Marcelo Branco]

Unclear... Can you elaborate and provide a more comprehensive example?

Domenic,

What i was trying to say is if, for some reason, the user wants to count 3 consecutives 1 also when there are 4, 5 , 6...or more consecutives 1 as in
1 1 1 1
1 1 1 1

or

1 1 1 1 1
1 1 1 1 1
1 1 1 1 1

your formula could provide the correct result with a small change as i suggested.

M.

 

[Marcelo Branco]

Trying to be more clear

1st example above: with 4 consecutives 1 you would have 2 sequences of 3 consecutives 1

2nd example above: with 5 consecutives 1 you would have 3 sequences of 3 consecutives 1

Count = NumberOfCconsecutives1 - 2
(only if NumberOfConsecutives1 >=3)

M.

 

[jsini]

Hello Brother,
I am Jatinder from India. You can use coutnif(Range,lookup value). Suppose you have some certain data with repetitive number in cell A2: A28. Apply this function countif(Select the range A2:A28(fix this range by pressing F4),select the number which you want to search the count). It will work perfectly.

Byeeeeeeeeee

 

[Marcelo Branco]

Hello Brother,
I am Jatinder from India. You can use coutnif(Range,lookup value). Suppose you have some certain data with repetitive number in cell A2: A28. Apply this function countif(Select the range A2:A28(fix this range by pressing F4),select the number which you want to search the count). It will work perfectly.

Byeeeeeeeeee

Hi,

humm...i think this doesnt work. The range has 0s or 1s in different cells not 111 or 1111 in one cell. Think a little bit more.

M.

 

[pgc01]

Hi Marcelo

If I understand correctly then try:

=SUMPRODUCT((A2:Z2)*(B2:AA2)*(C2:AB2))

 

[Marcelo Branco]

Hi Marcelo

If I understand correctly then try:

=SUMPRODUCT((A2:Z2)*(B2:AA2)*(C2:AB2))

Nice!!!

Works prefectly (i'm trying to understand how... )

M.