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.