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.
Comments
-
Give this a try...
=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(SumCompSched@row)), CHILDREN(SumCompSched@row), 0))
-
Putting that in a new column gives #NO MATCH.
-
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))
-
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).
Help Article Resources
Categories
Check out the Formula Handbook template!