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

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

Thanks,
Alexis.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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

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:

#### 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

#### Aladin Akyurek

##### MrExcel MVP

ADVERTISEMENT

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:
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

ADVERTISEMENT

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

##### 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

Replies
7
Views
120
Replies
1
Views
84
Replies
8
Views
220
Replies
1
Views
135
Replies
10
Views
271

Threads
1,118,424
Messages
5,572,026
Members
412,433
Latest member
talania01