Count Ranking Rounded Up

I need help with creating a formula that rounds the rank of a person up for a tied Count. Situation: “Name 05” has a count of 9. The formula needs to return the first Rank, which would result in 98 (not 100). 

Agent: Count: Rank: 

Name 01 10 96 

Name 02 10 97 

Name 03 9 98 

Name 04 9 99 

Name 05 9 100 

Name 06 9 101 

Name 07 9 102 

The Formula: =SUMPRODUCT(COUNTIF($A$4:$A$50,Agent!$A$4:$A153)) 

“$A$4:$A$50” is the list of agents I’m looking for, while Agent!$A$4:$A153 returns the rank from the data sheet (which needs to be edited to return the first rank of that count)

By: Justin

Leave a Reply

Your email address will not be published. Required fields are marked *