count number of times a value appears in a column

[SQUIDD]

Dear all

Is there a way of counting how many times something appears in a column before the value changes.
below NO appears 10 times in a row, this being the max number of times in a row.

yes
yes
yes
yes
no
no
no
yes
no
no
no
no
no
no
no
no
no
no
yes
yes

Thanks

Dave

 

[circledchicken]

Hi,

Perhaps try:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]List[/TD]
[TD][/TD]
[TD]Value[/TD]
[TD]Max consecutive count[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD]no[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
D2	=MAX(FREQUENCY( IF(A2:A21=C2,ROW(A2:A21)), IF(A2:A21<>C2,ROW(A2:A21))))

<tbody>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Note:

• This method is described by Domenic here: Count Consecutive Numbers
• Other similar implementations include:
  • Count singles,double,tripple: http://www.mrexcel.com/forum/excel-questions/601194-count-singles-double-tripple.html
  • Start Position Of Most Consecutive 1s in Column: http://www.mrexcel.com/forum/excel-questions/649571-start-position-most-consecutive-1s-column.html
  • Formula to count how many times adjacent cells in a row fulfil a certain criteria: http://www.mrexcel.com/forum/excel-...-row-fulfil-certain-criteria.html#post3256085

 

[Aladin Akyurek]

Dear all

Is there a way of counting how many times something appears in a column before the value changes.
below NO appears 10 times in a row, this being the max number of times in a row.

yes
yes
yes
yes
no
no
no
yes
no
no
no
no
no
no
no
no
no
no
yes
yes

Thanks

Dave

Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:A23="no",ROW(A2:A23)),IF(A2:A23="yes",ROW(A2:A23))))

 

[SQUIDD]

thanks guys

that worked great, just one more thing, can you count how many times the number of times a maximum is reached and can i use the same formula but use the large function for finding secon highest etc.

i guess i will just try that one.

thanks again

Dave

 

[Aladin Akyurek]

thanks guys

that worked great, just one more thing, can you count how many times the number of times a maximum is reached and can i use the same formula but use the large function for finding secon highest etc.

i guess i will just try that one.

thanks again

Dave

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]Data
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]yes
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]yes
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]List
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]yes
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]no
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

C2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A23="no",ROW(A2:A23)),
  IF(A2:A23="yes",ROW(A2:A23)))>1,1))

C4, control+shift+enter and copy down:

=IF(ROWS($C$4:C4)<=$C$2,LARGE(FREQUENCY(IF($A$2:$A$23="no",
  ROW($A$2:$A$23)),IF($A$2:$A$23="yes",ROW($A$2:$A$23))),ROWS($C$4:C4)),"")