Counting
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Counting

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Surfers Paradise
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a column that has text entered into it
    like the following.
    1-1
    1-1
    1-2
    1-3
    1-4
    1-4
    I want to be able to count the number of sets that occur more than once.
    ie the above would have aresult of 2 being 1-1 & 1-4

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using the countif function:

    =IF(COUNTIF($A$1:$A$6,A1)>1,COUNTIF($A$1:$A$6,A1),"")

    Where $A$1:$A$6 is your data range and A1 is the value you are searching for. The function will only display values that occur more than once.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,771
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-18 13:41, Lewy2 wrote:
    I have a column that has text entered into it
    like the following.
    1-1
    1-1
    1-2
    1-3
    1-4
    1-4
    I want to be able to count the number of sets that occur more than once.
    ie the above would have aresult of 2 being 1-1 & 1-4
    =SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>1,1))

    where A2:a7 houses the sample you provided.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Surfers Paradise
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 14:03, Aladin Akyurek wrote:
    On 2002-03-18 13:41, Lewy2 wrote:
    I have a column that has text entered into it
    like the following.
    1-1
    1-1
    1-2
    1-3
    1-4
    1-4
    I want to be able to count the number of sets that occur more than once.
    ie the above would have aresult of 2 being 1-1 & 1-4
    =SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>1,1))

    where A2:a7 houses the sample you provided.
    I cant Get either of the relies to work thanks for the effort
    Regards
    Lewy

  5. #5
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    They need to be entered by pressing Control-Save-Enter at the same time instead of just enter since they are array, oops I mean CSE, formulas.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,771
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      

    > I cant Get either of the relies to work

    Lewy,

    What happens when you apply the FREQUENCY formula?

    Aladin

User Tag List

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