+ Reply to Thread
Results 1 to 5 of 5

Count number of unique instances based on fixed ID numbers

  1. #1
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Count number of unique instances based on fixed ID numbers

    Hello,

    I am working in a larger data set, and am having a hard time with ID numbers which can show up more than once. The database captures the amount of leads generated and tracks them through different status points. An ID number for a given lead can show up multiple times depending on how many meetings are associated with that lead.

    In the attached:

    Problems:
    1) How would I be able to calculate the total number of raw leads generated when a lead can sit in "raw" status for more than one meeting? I am looking to count the first instance "raw" shows up in column D for each lead in column B
    2) How can I capture the most recent number of leads under each status point? This involves counting the value in column D after finding the maximum value of a given ID (column B) in column C

    Bonus: Is there a formula I can drag down in column C that would show the next meeting number for each ID (I think it is just a count of the number of times the instance has shown up in column B)
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count number of unique instances based on fixed ID numbers

    OK. Easy part first:

    C4 copied down:
    =COUNTIF($B$4:B4,B4)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count number of unique instances based on fixed ID numbers

    With a list of the lead IDs in P2, use this in Q2, copied down:

    =COUNTIFS(B:B,P5,D:D,"Raw",C:C,1)

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count number of unique instances based on fixed ID numbers

    I don't follow what you want at 2). Please explain again and provide some sample answers (explaining how the answer was derived).

  5. #5
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Count number of unique instances based on fixed ID numbers

    Hi Glenn,

    Thanks for your help on the first two. What I am trying to count through the table in 2) is the status of all leads given their most recent meeting.

    For example, in the file that is attached LeadID 1 has appeared in 4 meetings, the status of LeadID at the most recent meeting (meeting number 4, or its highest value in column c) was "sold" so it would count one towards "sold"

    The answer for the spreadsheet as attached should be:

    Raw: 4 (LeadID 2, 3, 5, 6 are still in "Raw)
    Qualified: 0 (LeadID 1 moved through it, but is not there now)
    Sold: 2 (LeadID 1, 4)

+ 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 Unique Instances of Date while filtering based upon date and ID #
    By pmambrosetti in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2014, 02:22 PM
  2. Unique count of number of instances
    By arnab0711 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2013, 03:09 AM
  3. Count Unique instances across several Columns
    By Leigh.Odonnell in forum Excel General
    Replies: 5
    Last Post: 05-14-2012, 06:05 PM
  4. Replies: 5
    Last Post: 09-01-2011, 03:46 AM
  5. Count unique instances
    By meweaver27 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-28-2007, 09:57 AM
  6. Count the number of unique Numbers in a column
    By ajajmannen in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-10-2006, 01:10 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