Help with ranking based on criteria (RANKEQ, COLLECT)

Hello,

I am trying to rank location names by the number of activities they've accomplished within their specific region.

Dummy data:


When I searched the forum, I found a few examples of RANKEQ with COLLECT. However, I receive errors when I insert this:

=RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, "Gulf Coast", 0))

Can you point me in the right direction? Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/06/22 Answer ✓

    Hi @curiouslearner

    You're very close! It looks like you just have one of the closing parentheses in the wrong place.

    This is the structure:

    =RANKEQ(number to rank, range with numbers, order of rank)

    We're replacing the middle range portion with a COLLECT function to filter the range, like so:

    =RANKEQ(number to rank, COLLECT(...), order of rank)

    Which spelled out becomes:

    =RANKEQ(number to rank, COLLECT(range with numbers, criteria, "criteria"), order of rank)


    In your formula, we're missing the closing ) for the Collect function, before moving on to the order. Now, the "Criteria" in the Collect function can be manually typed in, like you have, OR we could reference a cell, which is what I would suggest.

    Try:

    =RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, Region@row), 0)


    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/06/22 Answer ✓

    Hi @curiouslearner

    You're very close! It looks like you just have one of the closing parentheses in the wrong place.

    This is the structure:

    =RANKEQ(number to rank, range with numbers, order of rank)

    We're replacing the middle range portion with a COLLECT function to filter the range, like so:

    =RANKEQ(number to rank, COLLECT(...), order of rank)

    Which spelled out becomes:

    =RANKEQ(number to rank, COLLECT(range with numbers, criteria, "criteria"), order of rank)


    In your formula, we're missing the closing ) for the Collect function, before moving on to the order. Now, the "Criteria" in the Collect function can be manually typed in, like you have, OR we could reference a cell, which is what I would suggest.

    Try:

    =RANKEQ([Total Activities]@row, COLLECT([Total Activities]:[Total Activities], Region:Region, Region@row), 0)


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!