+ Reply to Thread
Results 1 to 6 of 6

Produce Count of Instances a List Has Repeat Text

  1. #1
    Registered User
    Join Date
    11-11-2020
    Location
    Nashville, TN
    MS-Off Ver
    2016
    Posts
    2

    Produce Count of Instances a List Has Repeat Text

    I am trying to produce a function for a dashboard which shows the number names on a list that appear more than once.

    For example in below list the cell would populate the number 3 since there are 3 names that have ≥2 occurrences:
    Andy
    Andy
    Andy
    Ben
    Jake
    Jake
    Sarah
    Sarah
    Sarah
    Sarah

    Any help is greatly appreciated and let me know if more information is required.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Produce Count of Instances a List Has Repeat Text

    Hi & welcome to the board.
    How about
    =SUM(--(FREQUENCY(MATCH(A2:A11,A2:A11,0),ROW(A2:A11)-ROW(A2)+1)>=2))
    Needs to be confirmed with Ctrl Shift Enter, rather than just Enter.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Produce Count of Instances a List Has Repeat Text

    ---Deleted------
    Last edited by Limor_OP; 11-11-2020 at 11:10 AM.

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Produce Count of Instances a List Has Repeat Text

    C2=SUM(IF(FREQUENCY(IF(COUNTIF(A1:A100,A1:A100)>=2,MATCH(A1:A100,A1:A100,0)),ROW(A1:A100)-ROW(A1)+1),1))

    Control+shift+enter

  5. #5
    Registered User
    Join Date
    11-11-2020
    Location
    Nashville, TN
    MS-Off Ver
    2016
    Posts
    2

    Re: Produce Count of Instances a List Has Repeat Text

    Those did the trick! Thank you both!

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Produce Count of Instances a List Has Repeat Text

    You're welcome & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count instances of same entry in a list
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2017, 10:25 AM
  2. [SOLVED] Count Instances of Repeat and Non-Repeat Dates
    By mgs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2015, 09:54 PM
  3. [SOLVED] Count to produce a list if dates.
    By JO505 in forum Excel General
    Replies: 6
    Last Post: 05-09-2015, 02:28 PM
  4. [SOLVED] VBA to List each count of instances of a given value in another column
    By mfitzgerald94 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2013, 05:24 PM
  5. how to count text and produce bar graph from results
    By blake86 in forum Excel General
    Replies: 3
    Last Post: 07-01-2009, 05:11 PM
  6. Replies: 6
    Last Post: 06-05-2009, 12:03 PM
  7. [SOLVED] How do I count the instances of numbers in a list?
    By John@NGC in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-18-2006, 04:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1