Solver to find the most possible options with out going over.

[AWM21]

Hello, thank you for looking at this with me.

I have 13 options and I need to select the highest count of these options, where their items costs totals cannot exceed a fixed number. (168) in this example.
Out of the options below, the goal is to include as many options without exceeding the fixed total. The totals can fall short of 168, just not exceed.

I used solver, but solver gets me the least amount of options to include, I need the most.

here is my data. Thanks again!

Book1.xlsx
	B	C	D	E	F	G	H	I	J
8
9		Opt 01	80	1	80		Total of all options	760
10		Opt 02	63	1	63		Opt items totals cannot exceed	168
11		Opt 03	18	1	18		Difference between above	592
12		Opt 04	80	1	80
13		Opt 05	36	1	36
14		Opt 06	80	1	80
15		Opt 07	60	1	60
16		Opt 08	80	1	80
17		Opt 09	80	1	80
18		Opt 10	80	1	80		Total post Solver solution	760
19		Opt 11	54	1	54
20		Opt 12	21	1	21
21		Opt 13	28	1	28
22
Sheet1

Cell Formulas
Range	Formula
I9	I9	=SUM(F9:F21)
I11	I11	=I9-I10
I18	I18	=SUM(F9:F21)
F9:F21	F9	=E9*D9

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$E$9:$E$21	F9
solver_lhs1	=Sheet1!$E$9:$E$21	F9
solver_lhs2	=Sheet1!$E$9:$E$21	F9
solver_lhs3	=Sheet1!$E$9:$E$21	F9
solver_rhs4	=Sheet1!$I$10	I11

Book1.xlsx
	B	C	D	E	F	G	H	I	J
8
9		Opt 01	80	0	0		Total of all options	168
10		Opt 02	63	0	0		Opt items totals cannot exceed	168
11		Opt 03	18	0	0		Difference between above	0
12		Opt 04	80	0	0
13		Opt 05	36	0	0
14		Opt 06	80	0	0
15		Opt 07	60	1	60
16		Opt 08	80	0	0
17		Opt 09	80	0	0
18		Opt 10	80	1	80		Total post Solver solution	168
19		Opt 11	54	0	0
20		Opt 12	21	0	0
21		Opt 13	28	1	28
22
Sheet1

Cell Formulas
Range	Formula
I9	I9	=SUM(F9:F21)
I11	I11	=I9-I10
I18	I18	=SUM(F9:F21)
F9:F21	F9	=E9*D9

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$E$9:$E$21	F9
solver_lhs1	=Sheet1!$E$9:$E$21	F9
solver_lhs2	=Sheet1!$E$9:$E$21	F9
solver_lhs3	=Sheet1!$E$9:$E$21	F9
solver_rhs4	=Sheet1!$I$10	I11

 

[Peter_SSs]

Is this what you want?

22 12 24.xlsm
	C	D	E	F	G	H	I
9	Opt 01	80	0	0		Total of all options	163
10	Opt 02	63	0	0		Opt items totals cannot exceed	168
11	Opt 03	18	1	18		Difference between above	-5
12	Opt 04	80	0	0		Options used	5
13	Opt 05	36	1	36
14	Opt 06	80	0	0
15	Opt 07	60	1	60
16	Opt 08	80	0	0
17	Opt 09	80	0	0
18	Opt 10	80	0	0
19	Opt 11	54	0	0
20	Opt 12	21	1	21
21	Opt 13	28	1	28
Solver

Cell Formulas
Range	Formula
I9	I9	=SUM(F9:F21)
I11	I11	=I9-I10
I12	I12	=SUM(E9:E21)
F9:F21	F9	=E9*D9

 

[AWM21]

Is this what you want?

22 12 24.xlsm
	C	D	E	F	G	H	I
9	Opt 01	80	0	0		Total of all options	163
10	Opt 02	63	0	0		Opt items totals cannot exceed	168
11	Opt 03	18	1	18		Difference between above	-5
12	Opt 04	80	0	0		Options used	5
13	Opt 05	36	1	36
14	Opt 06	80	0	0
15	Opt 07	60	1	60
16	Opt 08	80	0	0
17	Opt 09	80	0	0
18	Opt 10	80	0	0
19	Opt 11	54	0	0
20	Opt 12	21	1	21
21	Opt 13	28	1	28
Solver

Cell Formulas
Range	Formula
I9	I9	=SUM(F9:F21)
I11	I11	=I9-I10
I12	I12	=SUM(E9:E21)
F9:F21	F9	=E9*D9

View attachment 81516

Thanks Peter! that works!
Looks like I was referencing the wrong cell for my objective, and I needed to select "Max" instead of defining a value.
Thanks again!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.