Formula help - adding criteria to LARGE?

Options
JLC
JLC ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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.

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!