Results 1 to 5 of 5

count number of times a value appears in a column

This is a discussion on count number of times a value appears in a column within the Excel Questions forums, part of the Question Forums category; Dear all Is there a way of counting how many times something appears in a column before the value changes. ...

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Location
    uk bexhill sussex
    Posts
    610

    Default count number of times a value appears in a column

    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
    Some one always knows more than me,thats why I am here.

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,748

    Default Re: count number of times a value appears in a column

    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
    Excel 2010
    Array Formulas
    Cell Formula
    D2 =MAX(FREQUENCY(
    IF(A2:A21=C2,ROW(A2:A21)),
    IF(A2:A21<>C2,ROW(A2:A21))
    )
    )
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
    Note:
    Last edited by circledchicken; Nov 11th, 2012 at 06:23 AM.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default Re: count number of times a value appears in a column

    Quote Originally Posted by SQUIDD View Post
    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))))
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Location
    uk bexhill sussex
    Posts
    610

    Default Re: count number of times a value appears in a column

    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
    Some one always knows more than me,thats why I am here.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default Re: count number of times a value appears in a column

    Quote Originally Posted by SQUIDD View Post
    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

    C2, control+shift+enter, not just enter:
    Code:
    =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:
    Code:
    =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)),"")
    
    Assuming too much and qualifying too much are two faces of the same problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com