# Help with ranking based on criteria (RANKEQ, COLLECT)

Options

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!

Options

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

Options

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

• Options

Thank you, thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!