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

#### a.whitton

##### New Member
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.

Thanks,
Alexis.

#### circledchicken

##### Well-known Member
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.

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:

#### VoG

##### Legend
(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 ##### MrExcel MVP
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.

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:
Rich (BB code):
``````=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.

Last edited:

#### circledchicken

##### Well-known Member
That would be CMD + Return for Mac Haha, oops, I've never used a Mac - thanks for the correction!

#### T. Valko

##### Well-known Member
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.

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

##### New Member
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:
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

##### Well-known Member
** 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

##### Well-known Member
CMD + Return for Mac What's a Mac? 