+ Reply to Thread
Results 1 to 6 of 6

Trying to find number of times an ID number appears in a data list

  1. #1
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Trying to find number of times an ID number appears in a data list

    I have a list 500 Master Account IDs, and a second sheet with a list of 1000+ child account IDs. Each of the child accounts is linked to a unique Master ID from the 1st sheet.

    I need to summarize as a count of how many child accounts each of the master account ID have.

    Trying to use SUMIF or COUNTIF, but unsure how to set the parameter to look up each of the different Master ID references. Do use a combined VLOOKUP. A little confused so any help would be greatly appreciated.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    Re: Trying to find number of times an ID number appears in a data list

    In general this applied to a colum will show you which are duplicates. =countif($A$2:A2,A2) dragged down. Dups show up as numbers greater than one.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,846

    Re: Trying to find number of times an ID number appears in a data list

    Hi Rohan,

    I think you need a Countif() formula like =COUNTIF(Child!C:C,Master!A2). See it in the attached example.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Trying to find number of times an ID number appears in a data list

    So if the Master account ID's are listed in Column A of the 1st referenced, then referenced accordingly in the 2nd Child Account sheet, in Column C; how to I count how many times the Master ID is referenced in teh second sheet.

    For example if the Master AC has 12 child account locations in the second sheet, i need to count how many times this appears as a summary on 1st master sheet.

    Apologies if my question is vague or confusing, I do appreciate the support

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,400

    Re: Trying to find number of times an ID number appears in a data list

    Hi,

    If each of the child ids on the second sheet have the master ID repeated on the same row then a simple COUNTIF will do. i.e. Assuming sheet1 has the master IDs in A1:Ann and the second sheet has the child ids in column A and their master IDs in each cell in column B, then in B1 of sheet1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Of course if the child ids in sheet2 are grouped by master ID. e.g.

    Child1 Master1
    Child2
    Child3
    Child4 Master2
    Child5
    Child 6
    ....

    then a different approach will be needed
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: Trying to find number of times an ID number appears in a data list

    Perfect thanks team. I was definitely over thinking that and over complicating the formula to use.

    Thank you so much.

+ 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. Find number of times a value from a list appears in a seperate column
    By GSmith8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 02:05 AM
  2. Counting the number of times a month appears in a list
    By john dalton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-16-2013, 12:48 PM
  3. Replies: 3
    Last Post: 04-26-2013, 01:21 PM
  4. [SOLVED] find number of times a letter or a number appears in a column
    By dcoates in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2012, 02:47 PM
  5. Replies: 6
    Last Post: 03-02-2010, 04:15 PM
  6. Counting the number of times a date appears in a list
    By Research RN in forum Excel General
    Replies: 7
    Last Post: 11-25-2008, 12:50 PM
  7. Replies: 0
    Last Post: 08-28-2005, 10:37 AM

Tags for this Thread

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