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.

 

[T. Valko]

Great, thanks for clarifying and for your speedy help!! I really appreciate it.

You're welcome. Thanks for the feedback!

 

[Batch21]

Hi there,

Sorry to dredge up an old thread, but I have been using the output of this thread to vet some data outputs and was wondering if it is possible to alter the output of this formula to count the number of consecutive blanks and/or zeros?

=MAX(FREQUENCY(IF(OFFSET(L3,0,0,1,COUNTA($L$2:$AK$2))="",COLUMN(OFFSET(L3,0,0,1,COUNTA($L$2:$AK$2)))),IF(OFFSET(L3,0,0,1,COUNTA($L$2:$AK$2))<>"",COLUMN(OFFSET(L3,0,0,1,COUNTA($L$2:$AK$2))))))

This formula is specific to counting blanks, I'm usually pretty proficient in excel but the use of frequency has scuppered my chances as I'm not too familiar with this formula...

 

[Aladin Akyurek]

Hi there,

Sorry to dredge up an old thread, but I have been using the output of this thread to vet some data outputs and was wondering if it is possible to alter the output of this formula to count the number of consecutive blanks and/or zeros?
[...]

Assumin that A2:L2 below...

[TABLE="width: 576"]
<COLGROUP><COL style="WIDTH: 48pt" span=12 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]vad[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]kad[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]jad[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

should yield 5 as result:

Control+shift+enter, not just enter...

=MAX(FREQUENCY(IF((A2:L2="")+(A2:L2=0)>0,COLUMN(A2:L2)),IF(1-((A2:L2="")+(A2:L2=0)>0),COLUMN(A2:L2))))