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
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