Can anyone help me generate a formula for this??

[amerain]

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

 

[Debraj]

Hi Amy,

Welcome to the forum..


Can you please elaborate..

Why "Person 3" is 1..

* Can I assume.. Your Count Criteria is only dependent with Person & Type.. All other are not the Unique Decider Factor..


Please refer to below posts.. or upload a sample file..

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[SirJB7]

Hi, amreain!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Should this help? It has just come out of the oven:

http://chandoo.org/forums/topic/sum-of-top-3-records-based-on-filtered-criteria#post-103195


Regards!

 

[Sajan]

Hi Amy,

Here is my initial attempt at a formula for generating a list of the top "n" persons. So as to shorten the formula length (of an already long formula!), I have used a helper column. The formula for the helper column is as follows, and is to be array-entered over the whole range:


=TRANSPOSE(TRANSPOSE(SIGN((MATCH(Owner,Owner,0)>=(ROW(Owner)-MIN(ROW(Owner))+1))*MATCH(Owner,Owner,0)))*MMULT(TRANSPOSE(ROW(Owner)^0), (Owner=TRANSPOSE(Owner))*MMULT((TypeCol=TypeCodes)*(YearCol=YearCodes)*(DateCol=DateCodes)*MMULT((TotalDays+0>DateThreshold)*1, TRANSPOSE(COLUMN(TotalDays)^0)), TRANSPOSE(COLUMN(TypeCodes)^0))))


entered with Ctrl + Shift + Enter


This helper column has been named "counts".


The source data, with the helper col looks as follows:

Owner            Year    Type    Date            TotalDays    Counts
Person 1    2011    R    5/10/2013    2    0    1
Person 2    2012    B    5/10/2013    3    2    2
Person 3    2013    A    5/11/2013    2        1
Person 4    2011    R    5/10/2013    3    1    1
Person 5    2012    E    5/11/2013    2        0
Person 1    2011    R    5/10/2013    3    1    0
Person 2    2013    A    5/10/2013    3    2    0
Person 3    2013    A    5/10/2013    3    1    0

Then, to generate the list of the names, the following formula is used (shown for cell D13):

=INDEX(Owner, SMALL(IF(LARGE(Counts, ROW(A1))=IF((COUNTIF($D$12:D12, Owner)=0)*(Counts>0), Counts), ROW(Owner)-MIN(ROW(Owner))+1), 1))


enter with Ctrl + Shift + Enter


Copy down to additional rows


Then, for each of the persons, to get the counts (shown for cell E13):

=SUMIF(Owner,D13,Counts)


See following workbook for the formulas in action:

http://speedy.sh/dUgm4/Chandoo-Summary-Counts-for-Amy.xlsx


Since the formulas do not assume anything about the number of rows in the source data, etc., you should be able to adapt it for your needs easily. I have also setup the criteria in a table, so that you can revise it as needed.


It is a bit late in the day here now. I will give some thought to try and optimize/shorten the formulas a bit... tomorrow or so.


Cheers,

Sajan.

 

[SirJB7]

@Sajan

Hi!

If that's the inital attempt Chandoo should upgrade the hosting server for storing the final version

Regards!