Formula help - adding criteria to LARGE?

Jaye Casciano

Seeking some advice on how to make the LARGE function play nice with criteria and possibly, I'm thinking, the COLLECT function. 

Here's our set up:

"Master project sheet" with:

1) "Project name" column

2) "2019 total hours" column which is a cross-sheet reference SUMIFS formula

3) "Project priority score" column which is a SUM formula value from 1 to 100

"=LARGE({MASTER 2019 total project hours}, n)" works great to get the value of the nth-most highly allocated hours. What I need to add to this formula is the following:

- only show the nth largest IF "Project priority score" is < 50

- instead of showing me the value of the allocated hours, return the name from the "Project name" column

So, in the end, the formula will produce the name of the project that has a priority score of under 50 and the highest number of allocated hours.



Hope that makes sense and many thanks to whomever can help me figure this bad boy out! I just can't get my nests to play nice and haven't worked with LARGE enough to know what I'm doing wrong.

Paul Newcome

Try something along the lines of 

 

=MAX(COLLECT({Hours}, {Priority}, @cell < 50))

 

This will give you the largest number of hours for any project with a priority under 50.

 

You would then implement this into another function that is able to pull data in order to pull the Project Name.

Jaye Casciano

Thank you kindly Paul for trying to help! However I should clarify - I need to be able to show the nth largest, from 1st to 10th. I've updated the post now.

Paul Newcome

Ah. Ok. Just replace MAX with LARGE, and add a 

 

, ## in between the two ending parenthesis.

 

=LARGE(COLLECT({Hours}, {Priority}, @cell < 50), ##)

Jaye Casciano

Thanks Paul, that's very helpful - however I admit I am not sure how to connect this to then telling me the project name instead of the number of hours. Would you be willing to spell it out for me? 

Also wondering if there's a way to point the formula to two separate sheets? We have two project lists; one confidential and one non-confidential so it would be beneficial to collect the 10 largest projects' names from across those two areas.



Many thanks again for your insight! 

Paul Newcome

This solution will depend on a couple of things.

1. Are you wanting to pull the Project Name from both sheets or are you wanting to pull only one Project Name after comparing both sheets?

 

2. What are the chances that the number of hours is NOT good enough for a unique ID? We will need to establish some type of unique ID to narrow down the results to just one (or just one from each sheet).

 

Please note: These questions are relevant to using the LARGE function for a top 10 (or top whatever number you want). For each place (1st - nth) we will need to know these things, but if we can establish it like we are looking for the top 1, we can easily use that to establish the rest of the places as well.