Max value with min collect

I know this is need of a combination of things, but I cant figure it out unless I use a range of cells. I would prefer to search the collective column to perform the task.

I want to return the name of the Visit Provider who has the greatest number of SumCompSched for each Phase & Clinic.

I appreciate the help in advance.

Find more posts tagged with

Healthcare

Formulas

Comments

Paul Newcome

Give this a try...

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(SumCompSched@row)), CHILDREN(SumCompSched@row), 0))

ateleoh

Putting that in a new column gives #NO MATCH.

Paul Newcome

I didn't realize you were putting it in a different column. Give this a try...

=INDEX(CHILDREN([Visit Provider]@row), MATCH(MAX(CHILDREN(SumCompSched@row)), CHILDREN(SumCompSched@row), 0))

ateleoh

Thank you. This works for finding the Visit Provider with the overall highest NumOfVisits, but I need to show who has the highest in each Clinic(Phase & Clinic).

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?