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 1-3

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 20-30 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?

Find more posts tagged with

Accepted answers

All comments

Ash A.

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?

KPH

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 0-10 to be ranked 1, 11-20 to be ranked 2, and 21-30 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.

JPavlasek

@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:

1-20 = 4

21-40 = 3

41-60 = 2

61-80 = 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"))))

KPH

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.

JPavlasek

@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!

KPH

Wonderful! Troubleshooting is a great way to learn. You'll be able to adjust that formula to do all sorts of thing. 😍

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.