Problem with function INDEX, SMALL AND IF

Hello everyone,

I am new to this forum and I am trying to create a sheet where, when I list all the quantities of ingredients then they will show up on a side of the spreadsheet ranked from highest concentration to the lowest. As I may have more than one ingredient with the same concentration I couldn't use Lookup so I used the index function.

Here is the function

Formula:

Please Login or Register  to view this content.

Everything work great except when I have for example 3 ingredients with a concentration of 300g and then 5 ingredients with a concentration of 200g. In this case I figured out that I would need to get the ROW(2:2) function back to ROW(1:1) when it changes from one weight to another. I don't know how to do that. I am sorry if you didn't understand what I am trying to explain. I enclosed a file with an example of what I am trying to do. You will see the first row is the problem I have and the second the formula how it should be generated in order to work.

I am a french native so I am sorry if my english is not proper as it is my second language.

Thank you!

Jules

I will try with countif. And yes I did use the array formula but I am on mac so it is command + enter. I will keep you posted! Thank you for taking time on that!

Ok it works great, thank you so much. Could you explain to me what does COUNTIF do in that function to make it work. It's ok if you doesn't have the time! Thank you again everybody!