+ Reply to Thread
Results 1 to 4 of 4

Counting Unique Values and Generating an Ordered List

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Counting Unique Values and Generating an Ordered List

    Hello,
    I have a data set where I need to evaluate several conditions and then return an ordered list based on the number of records meeting the specified conditions. My data is arranged as follows:

    Owner: Year: Type: Date: #Days:
    Person 1 2011 R 5/10/13 2
    Person 2 2012 B 5/10/13 3 2
    Person 3 2013 A 5/11/13 2
    Person 4 2011 R 5/10/13 3 1
    Person 5 2012 E 5/11/13 2
    Person 1 2011 R 5/10/13 3 1
    Person 2 2013 A 5/10/13 3 2
    Person 3 2013 A 5/10/13 3 1

    I have been calculating the totals for each person in my table using the following: COUNTIFS(D:D,5/10/2013,Type,"A",Year,2013,TotalDays,">2",Owner,A2)+COUNTIFS(D:D,5/10/2013,Type,"E",Year,2012,TotalDays,">2",Owner,A2)+COUNTIFS(D:D,5/10/2013,Type,"B",Year,2012,TotalDays,">2",Owner,A2)+COUNTIFS(D:D,5/10/2013,Type,"R",Year,2011,TotalDays,">2",Owner,A2)

    This does what I need it to do, but instead of generating a table for everyone, (Persons 1 through 5), I just need to generate a table for the people whose result of the above formula are the top 3 largest values. The resulting table for this example being:

    Owner Total
    Person 2 2
    Person 1 1
    Person 3 1

    Is there anyway to do that? I tried removing the owner criteria from my countifs statement but can get the unique list of values I need after that. Any thoughts on doing this?

    Thanks!!
    Amy

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Counting Unique Values and Generating an Ordered List

    maybe with an pivot table.

    see the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Counting Unique Values and Generating an Ordered List

    Please upload your file.Its simple to solve.

  4. #4
    Registered User
    Join Date
    03-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Counting Unique Values and Generating an Ordered List

    Hello,
    Unfortunately I cannot upload the file because it has personalized information in it. Do you have any other suggestions?
    Thanks!
    Amy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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