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.

 

[Oeldere]

Maybe a way to start.

See the link.

Uw bestand, count for 4 items (oeldere).xlsx, is succesvol geupload

 

[circledchicken]

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.

Hi,

Perhaps try this (note that you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

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

This assumes that 0,1,2 and 3 are the only permitted values in the range. If not, it will need some amendment.

This method is described by Domenic here: Count Consecutive Numbers
Other similar implementations are described here:

• Count singles,double,tripple
• http://www.mrexcel.com/forum/excel-questions/649571-start-position-most-consecutive-1s-column.html

 

[VoG]

(note that you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula)

That would be CMD + Return for Mac

 

[Aladin Akyurek]

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.

3

0

1

0

0

1

1

2

3

0

0

2

2

1

3

0

<colgroup><col style="width: 48pt;" span="16" width="64"> <tbody>
</tbody>

A1, control+shift+enter (CMD + Return for Mac), not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(B1:P1,{1,2,3},0)),COLUMN(B1:P1)),
  IF(1-ISNUMBER(MATCH(B1:P1,{1,2,3},0)),COLUMN(B1:P1))),1))

Adjust the horizontal data range to suit.

 

[circledchicken]

That would be CMD + Return for Mac

Haha, oops, I've never used a Mac - thanks for the correction!

 

[T. Valko]

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.

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.

 

[a.whitton]

Hi,

Perhaps try this (note that you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

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

This assumes that 0,1,2 and 3 are the only permitted values in the range. If not, it will need some amendment.

This method is described by Domenic here: Count Consecutive Numbers
Other similar implementations are described here:

• Count singles,double,tripple
• http://www.mrexcel.com/forum/excel-questions/649571-start-position-most-consecutive-1s-column.html

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.

 

[circledchicken]

** 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.

CMD + Return for Mac

 

[T. Valko]

CMD + Return for Mac

What's a Mac?