Ranking Entries
I have a scoring smartsheet where we are ranking applicants based on a score. I would like to set up a column that automatically ranks them BASED on how high or low a number is in another column.
ex: Column 1, Column 2, Column 3  all scores
Column 4  Formula entered to automatically give me the total of Columns 13
Column 5  Rank number assigned BASED on how high or low the number in Column 4 is.
OR ALTERNATIVELY
I would also like to possibly assign a rank based on a range of numbers. If they score between 2030 points, then they would automatically be ranked as a 3.
I've played around with CountIf formulas and can't seem to figure out the magical one.
is that possible?
Answers

The main workaround that comes to mind is creating a Report driven from this Sheet where the rows are arranged based on total scores. Let's say you have a 'Total Score' column which gives you the total based on Column 1, 2, & 3. You would then use a Report with the same columns, however you would set a 'Sort  Descending to Ascending' in order to get the highest number at top, and the lowest at the bottom.
Does that help?

Hi @JPavlasek
I think you are close, the formula you would need for Colum 4 is a simple SUM, not a COUNTIF. COUNT will count the cells, SUM will add up the values within the cells.
=SUM([Column1]@row, [Column2]@row, [Column3]@row)
The formula above will give you the total score. As shown here:
Then in Column 5 you can use an IF.
In this example the value in Column5 is a text string based on whether or not the value in column 4 is less than 10 or not
=IF([Column4]@row < 10, "less than 10", "10 or more than 10")
You can replace the text output with a second formula. For example:
=IF([Column4]@row < 10, "less than 10", IF([Column4]@row < 20, "not less than 10 but less than 20", "20 or more"))
This second IF is only evaluated if the first is false. Hence why I am describing this as "not less than 10 but less than 20". Here is how that looks:
You can continue to nest IFs. In your case I think you want 010 to be ranked 1, 1120 to be ranked 2, and 2130 to be ranked 3.
This formula does that. Notice I am using <= to mean less than or equal to. You may want to change this.
=IF([Column4]@row <= 10, "1", IF([Column4]@row <= 20, "2", IF([Column4]@row <= 30, "3", "")))
This part at the end "" means the formula will return nothing if the score is more than 30. You can also omit it (and the preceding comma) but I have left it in as a place holder in case you need more IFs (you would replace "" with your IF).
I hope this helps.

@KPH This ABSOLUTELY helps, THANK YOU! I think I'm definitely understanding better HOW to formulate a countIF formula overall, however I am STILL struggling.
The ranks are as follows:
120 = 4
2140 = 3
4160 = 2
6180 = 1
Here are the three different formulas I've constructed, neither is working for anything past the first statement. Rank 4 is working, but nothing else is. If the count is over 20 for the Grand Total it tells me #INCORRECT ARGUMENT SET
=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, "not less than 21", "3", IF([Grand Total]@row <= 60, "not less than 41", "2", IF([Grand Total]@row <= 80, "not less than 61", "1" ))))
=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, ">= 21", "3", IF([Grand Total]@row <= 60, ">= 41", "2", IF([Grand Total]@row <= 80, ">= 61", "1"))))
=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row >=21, "<= 40", "3", IF([Grand Total]@row >= 41, "<= 60", "2", IF([Grand Total]@row >= 61, "<= 80", "1"))))

Hi
In each of your examples you have an extra argument in each IF statement after the first one. These parts in bold should not be there.
=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, "not less than 21", "3", IF([Grand Total]@row <= 60, "not less than 41", "2", IF([Grand Total]@row <= 80, "not less than 61", "1" ))))
The syntax is …
logic, which is something like
[Grand Total]@row <= 40
Then comma
Then something to output or do if true, which you want to be the rank
1 or 2 or 3 or 4
Then comma
Then something to output or do false. Here you want another IF to be evaluated so you put in another formula.

@KPH OK I was definitely OVER complicating it. I guess I didn't realize that the 2nd, 3rd, 4th statements only happen if the prior one is not true, thus making it irrelevant to add the "not less than" portion. I just fixed it and now it's working. Thank you SO much for your help! not only is my formula working, but I now understand these statements a bit better!

Wonderful! Troubleshooting is a great way to learn. You'll be able to adjust that formula to do all sorts of thing. 😍
Help Article Resources
Categories
Check out the Formula Handbook template!