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

	A	B	C	D
1	List		Value	Max consecutive count
2	yes		no	10
3	yes
4	yes
5	yes
6	no
7	no
8	no
9	yes
10	no
11	no
12	no
13	no
14	no
15	no
16	no
17	no
18	no
19	no
20	yes
21	yes

<tbody>
</tbody>

Excel 2010

Array Formulas Cell Formula D2 =MAX(FREQUENCY( IF(A2:A21=C2,ROW(A2:A21)), IF(A2:A21<>C2,ROW(A2:A21)))) <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

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

Data		Count
yes		2
yes		List
no		4
no		2
no
no
yes
no
no

<TBODY>
</TBODY>

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)),"")