Comments on: How to find top or bottom N values in Excel

Comments page 2. Total comments: 54

1. 

   Slavyan says:
   2021-03-17 at 5:10 pm

   Hi, I have a list of names and next column I lave a list of the number of orders they have made for that month. How can I make another column next to it which can show their rank in who's made the most orders. Like next to the person with the most orders to have 1, second best to have 2 in the Rank column. And if second best makes more orders, the formula to automatically change into the correct ranking. Any ideas? Thank you very much in advance!

   1. 

      Alexander Trifuntov (Ablebits Team) says:
      2021-03-18 at 7:40 am

      Hello!
      If your data is written in columns A and B, then in column C write the formula

      =COUNTIF($A$2:$A$1000,">="&A2)

      Copy it down along the column.
      Hope this is what you need.

2. 

   Ian Witt says:
   2021-02-18 at 12:16 am

   Hi,
   I have a column of 50 numbers. I want to find the highest 18 numbers, add them together and return a total. Is there a formula that can do all this in one cell please?
   Cheers.

   1. 

      Alexander Trifuntov (Ablebits Team) says:
      2021-02-18 at 7:15 am

      Hello!
      Please use the following formula

      =SUM(LARGE(A1:A50,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}))

      You can learn more about LARGE function in this article: Excel LARGE function to get n-th highest value.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. 

         Ian Witt says:
         2021-02-18 at 10:22 am

         Hi, that returned the top score only, not the top 18?

         1. 

            Ian Witt says:
            2021-02-18 at 10:50 pm

            My bad. That works perfectly in Excel. I've been trying to use it in Google Sheets. By any chance do you have a solution for that?
            Kind regards, Ian.

            1. 

               Ian Witt says:
               2021-02-20 at 3:01 am

               Don't worry. Found the answer.

         2. 

            Alexander Trifuntov (Ablebits Team) says:
            2021-02-18 at 11:56 am

            Hello!
            I wrote this formula based on the description you provided in your original comment. The formula calculates the sum of the 18 largest numbers. If you want to get these numbers, then you need to do it in another cell.

            =LARGE(A1:A50,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})

3. 

   Yulia says:
   2020-11-21 at 12:23 am

   Hello Svetlana,

   Thank you very much for the posts! I have just started exploring the website looking for some personal tips for work with excel, and I must say that the format of the posts is very nice! Especially, thank you for taking time and posting screenshots with the clear explanations!

   1. 

      Svetlana Cheusheva says:
      2020-11-23 at 11:53 am

      Thank you for your positive feedback, Yulia! It's the best incentive for us to keep up and improve :)