PDA

View Full Version : Counting Unique Values to Generate a List of the Top X Largest



abbab
05-14-2013, 07:22 AM
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

Teeroy
05-19-2013, 06:19 AM
Hi Amy,

The counting appears suited to using a Pivot Table with:
Owner in Row,
Year, Type, #Days in Column; and
count of Date in Data.
You then set the criteria for the Columns of the PivotTable to extract the count.
AutoSort|Autoshow of the top three can be shown in the Advanced Field setting for the Owner.

Please note the descriptions I use are for excel 2003 and from memory they may vary slightly with later versions of excel.

HTH.