Sum based conditional sorting

Here is a solution that uses Solver. Solver is an add-in. Do a web search for your version of Excel to see how to add it in.

The issue with solver is to set up the problem. In this case I added two extra columns: Buy and Value. Buy has no formulas, Solver will fill this in. Value has the formula =B2*E2. So if B2 is zero then E2 is zero and if B2 is 1 then E2 is whatever B2 is.

The target cell is F23 and it has the formula: =SUM(F2:F20)

We are now ready for solver.

We want the objective Cell (F23) to be equal to 67.

By changing the cells in the ranage E2:E20 (Buy) - but these cells need constraining otherwise solver might want to buy more than one item or it might want to buy fractional items. So our constants are:

E2:E20 <=1 and E2:E20 >= 0 - this means that the value has to be between 0 and 1. However, this is not enough since there are an infinite number of values between zero and one. So we also add the constrain that it must also be an integer. Given these constraints, the only possible values are zero and one.

Click on the Solve button and solver will put a one or zero in the Buy column. Select the rows with the ones. Note that this may be only one of multiple possible solutions.

On Sheet 2, I solved a slightly different problem in case it is the one you are actually trying to solve. I wanted to maximize the Value / Cost. So I set up a new target in Cell G23, and this time an additional constraint is that F23 = 67.

I decided to give it a try, but got an error stating that objective cells must be formulas.

Strange, could you please test uploaded file and see if this work for you.

Alf

Ps When I uploaded the file for testing, objective cell was set to H13 or something like that but not to F23 as it should be. This would give the error you got when testing.

I have two solver problems, one on sheet1 and the other on sheet2. That might be the issue.