Generate List From a List To Smallest Number to Largest Number

I have uploaded an Excel spreadsheet to help support this query.

I have a list of products (cell A - F)

Cell N1 has the number of availability

Cell P - U, I would like a formula to find the smallest to largest number from Column F then generate the total over to Cell P - U but only totalling the amount from Cell N1

I hope this makes sense, any help is appreciated.

Please add your expected results to the workbook manually to show what you are looking for.

Hi

I have added in manually with a comment what I am after. I hope that helps. Let me know if there is anything else I can do to help.

Can I just check that you are using Excel 2019 and not MS365?

Just in case you have MS365, here's a dynamic array function that will spill:

=LET(i,SORTBY(A2:F11,F2:F11,1),FILTER(i,SCAN(0,INDEX(i,,6),LAMBDA(x,y,x+y))<=N1))

Please update your forum profile, which says 2019.

Yes - just extend the ranges to suit your data, e.g.

=LET(i,SORTBY(A2:F110,F2:F110,1),FILTER(i,SCAN(0,INDEX(i,,6),LAMBDA(x,y,x+y))<=N1))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.