Formula to count how many times adjacent cells in a row fulfil a certain criteria

[a.whitton]

Hi,

I’m using Excel version 12.1.0 for Mac.

I’m having trouble making a formula that will allow me to recognize ‘episodes’ in rows of data. The criteria for an episode is that four or more adjacent cells (adjacent by column) need to have a value of 1,2 or 3.

I’d like the formula to count how many of these ‘episodes’ occur in each row.

E.g.
0 1 0 0 1 1 2 3 0 0 2 2 1 3 0 0 0 1 1 2 3 3 2 0 0 0

The formula would return a value of 3 for this row, as there are 3 lots of adjacent cells that fulfill the criteria for an ‘episode’.

My columns run from B to CG and rows run from 1 to 413 (no headers).
I’ve tried to think of a way to do this using the SUMIF function, but can’t work out how to write a formula for the ‘episode’ criteria.

If anyone has any suggestions for how to go about this it would be much appreciated!

Thanks,
Alexis.

 

[circledchicken]

Thank you so much for your help! This formula has worked perfectly. Can you tell me though, what part of the formula actually indicates the 'four or more' adjacent cells rule? i.e. If I wanted to make it 6 or more, what part would I change?

Thanks again!
Alexis.

Sorry, I realised I made a mistake, it should've been:

=SUM(
--(FREQUENCY(
IF(A1:Z1>0,COLUMN(A1:Z1)),
IF(A1:Z1=0,COLUMN(A1:Z1)))>3))

Although you might want to use some of the other solutions posted which account for values outside of 0,1,2 and 3 existing in your range.

 

[T. Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A1:Z1,{1,2,3},0)),COLUMN(A1:Z1)),
IF(ISNA(MATCH(A1:Z1,{1,2,3},0)),COLUMN(A1:Z1)))>3,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If the numbers will be only 0,1,2 or 3 then we can use this version:

=SUM(IF(FREQUENCY(IF(A1:Z1>0,COLUMN(A1:Z1)),IF(A1:Z1=0,COLUMN(A1:Z1)))>3,1))

Still array entered!

 

[a.whitton]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A1:Z1,{1,2,3},0)),COLUMN(A1:Z1)),
IF(ISNA(MATCH(A1:Z1,{1,2,3},0)),COLUMN(A1:Z1)))>3,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you for your help! Can I just clarify, is the '>3' part towards the end of the formula the part that stipulates the four or more adjacent cells rule?

Thanks,
Alexis.

 

[circledchicken]

What's a Mac?

I don't know...some weird contraption they told me about earlier

 

[T. Valko]

Thank you for your help! Can I just clarify, is the '>3' part towards the end of the formula the part that stipulates the four or more adjacent cells rule?

Thanks,
Alexis.

Yes.

Since the condition is "4 or more" we can express that in 2 different ways:

>=4
>3

I chose >3 because it's less typing!

 

[a.whitton]

Great, thanks for clarifying and for your speedy help!! I really appreciate it.

 

[Aladin Akyurek]

Great, thanks for clarifying and for your speedy help!! I really appreciate it.

Geez... Didn't you notice post #10?

 

[T. Valko]

Geez... Didn't you notice post #10?

Did you mean post #5?

The formula in that post returns incorrect results.

 

[Aladin Akyurek]

Geez... Didn't you notice post #10?

Should be Post #5... Plus I missed to add the criterion of >3...

 

[T. Valko]

Should be Post #5... Plus I missed to add the criterion of >3...

What would you do without me?