Max based on multiple criteria

M

mrjnice85

New Member
Joined
May 12, 2015
Messages
9
I tried searching high and low for an answer on this. I am trying to return the maximum value of an item under a particular spending threshold. So I have a sheet that has 3 products a budget and a cumulative costs. I need it to tell me the most of each of the products I can get as close to that budget amount as possible. In the example below I would need it to tell me 6 Pants, 7 Shirts, 2 Socks.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Current Qty[/TD]
[TD="align: center"]Max Qty[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Budget[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Cumulative Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Shirts[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]205[/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]Pants[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]1[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]Socks[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]2[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]2[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]3[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]3[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Socks[/TD]
[TD]1[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]4[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]4[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]5[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]5[/TD]
[TD]155[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts
[/TD]
[TD]6[/TD]
[TD]165[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Socks[/TD]
[TD]2[/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]6[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]7[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pants[/TD]
[TD]7[/TD]
[TD]220[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shirts[/TD]
[TD]8[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Socks[/TD]
[TD]3[/TD]
[TD]235[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Not sure i understand what you are looking for.

See if this is ok


[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][/tr]
[tr][td]
1​
[/td][td]
Product​
[/td][td]
Current Qty​
[/td][td]
Max Qty​
[/td][td] [/td][td]
Budget​
[/td][td] [/td][td]
Product​
[/td][td]
Qty​
[/td][td]
Cumulative Cost​
[/td][/tr]

[tr][td]
2​
[/td][td]
Shirts​
[/td][td]
0​
[/td][td]
7​
[/td][td] [/td][td]
205​
[/td][td] [/td][td]
Pants​
[/td][td]
1​
[/td][td]
20​
[/td][/tr]

[tr][td]
3​
[/td][td]
Pants​
[/td][td]
0​
[/td][td]
6​
[/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
1​
[/td][td]
30​
[/td][/tr]

[tr][td]
4​
[/td][td]
Socks​
[/td][td]
0​
[/td][td]
2​
[/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
2​
[/td][td]
50​
[/td][/tr]

[tr][td]
5​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
2​
[/td][td]
60​
[/td][/tr]

[tr][td]
6​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
3​
[/td][td]
70​
[/td][/tr]

[tr][td]
7​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
3​
[/td][td]
90​
[/td][/tr]

[tr][td]
8​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Socks​
[/td][td]
1​
[/td][td]
95​
[/td][/tr]

[tr][td]
9​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
4​
[/td][td]
105​
[/td][/tr]

[tr][td]
10​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
4​
[/td][td]
125​
[/td][/tr]

[tr][td]
11​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
5​
[/td][td]
135​
[/td][/tr]

[tr][td]
12​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
5​
[/td][td]
155​
[/td][/tr]

[tr][td]
13​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
6​
[/td][td]
165​
[/td][/tr]

[tr][td]
14​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Socks​
[/td][td]
2​
[/td][td]
170​
[/td][/tr]

[tr][td]
15​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
6​
[/td][td]
190​
[/td][/tr]

[tr][td]
16​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
7​
[/td][td]
200​
[/td][/tr]

[tr][td]
17​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Pants​
[/td][td]
7​
[/td][td]
220​
[/td][/tr]

[tr][td]
18​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Shirts​
[/td][td]
8​
[/td][td]
230​
[/td][/tr]

[tr][td]
19​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td]
Socks​
[/td][td]
3​
[/td][td]
235​
[/td][/tr]
[/table]

Array formula in C2 copied down
=MAX(IF($G$2:$G$19=A2,IF($E$2>=$I$2:$I$19,$H$2:$H$19)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
This problem is ugly because of the irregularity of appearance of pants shirts and socks. Nevertheless, first you can find the largest cumulative cost per type by using an array formula. Trues count as 1, and falses count as zero. Multiply whether it's pants/shirts/socks by whether it's below the budget by the cost itself. This formula goes in D2. Press ctrl-shift-enter to make it an array formula. The formula can by pasted down to D4.

=(MAX((H$2:H$19=B2)*(J$2:J$19 < F $ 2)<f$2)
*(J$2:J$19)))
(Forum would not display less than F dollar 2 without spaces in it)</f$2)
<f$2)
<f$2)

</f$2)
<f$2)
<f$2)

<f$2)* (J$2:J$19)))<F$2)*(J$2:j$19)))<f$2)*(j$2:j$19)))

Then, use a match-offset to find which row has the found cumulative cost and retrieve the max items. This formula goes in E2 and can be pasted down to E4.

=OFFSET(I$1,MATCH(D2,J:J,0)-1,0)

If you feel comfortable with the formulas you can combine the two into a single formula.</f$2)*></f$2)
</f$2)
</f$2)
 
Last edited:
Upvote 0
Marcelo Branco said:
Not sure i understand what you are looking for.

See if this is ok


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
Product​
[/TD]
[TD]
Current Qty​
[/TD]
[TD]
Max Qty​
[/TD]
[TD][/TD]
[TD]
Budget​
[/TD]
[TD][/TD]
[TD]
Product​
[/TD]
[TD]
Qty​
[/TD]
[TD]
Cumulative Cost​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
Shirts​
[/TD]
[TD]
0​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]
205​
[/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
1​
[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
Pants​
[/TD]
[TD]
0​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
1​
[/TD]
[TD]
30​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
Socks​
[/TD]
[TD]
0​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
2​
[/TD]
[TD]
50​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
2​
[/TD]
[TD]
60​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
3​
[/TD]
[TD]
70​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
3​
[/TD]
[TD]
90​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Socks​
[/TD]
[TD]
1​
[/TD]
[TD]
95​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
4​
[/TD]
[TD]
105​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
4​
[/TD]
[TD]
125​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
5​
[/TD]
[TD]
135​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
5​
[/TD]
[TD]
155​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
6​
[/TD]
[TD]
165​
[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Socks​
[/TD]
[TD]
2​
[/TD]
[TD]
170​
[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
6​
[/TD]
[TD]
190​
[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
7​
[/TD]
[TD]
200​
[/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Pants​
[/TD]
[TD]
7​
[/TD]
[TD]
220​
[/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Shirts​
[/TD]
[TD]
8​
[/TD]
[TD]
230​
[/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Socks​
[/TD]
[TD]
3​
[/TD]
[TD]
235​
[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in C2 copied down
=MAX(IF($G$2:$G$19=A2,IF($E$2>=$I$2:$I$19,$H$2:$H$19)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
Click to expand...

Awesome, that did the trick. I was close just missing the order of the If you had. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
How to sum a column - minus one row , to avoid circular calculations ?
Replies
6
Views
396
StrawberryDreams
S
E
  • Question Question
Help creating multiple lines
Replies
0
Views
335
Excelnewie
E
F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
277
frewert
F
K
  • Solved
Finding best selling item by sum of sales, for each salesperson
Replies
6
Views
544
Fluff
Fluff
Z
  • Solved
Drop down list using unique but with changed number of rows
Replies
5
Views
379
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top