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

a.whitton

New Member
Joined
Sep 1, 2012
Messages
4
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.
 

Some videos you may like

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
Joined
Aug 13, 2011
Messages
2,932
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:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

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

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

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
Joined
Sep 1, 2012
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

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