Max based on multiple criteria

[mrjnice85]

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.

	A	B	C	D	E	F	G	H	I
1	Product	Current Qty	Max Qty		Budget		Product	Qty	Cumulative Cost
2	Shirts	0			205		Pants	1	20
3	Pants	0					Shirts	1	30
4	Socks	0					Pants	2	50
5							Shirts	2	60
6							Shirts	3	70
7							Pants	3	90
8							Socks	1	95
9							Shirts	4	105
10							Pants	4	125
11							Shirts	5	135
12							Pants	5	155
13							Shirts	6	165
14							Socks	2	170
15							Pants	6	190
16							Shirts	7	200
17							Pants	7	220
18							Shirts	8	230
19							Socks	3	235

<tbody>
</tbody>

 

[Marcelo Branco]

Not sure i understand what you are looking for.

See if this is ok

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

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.

 

[krausr79]

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)

 

[mrjnice85]

Not sure i understand what you are looking for.

See if this is ok

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

<tbody>
</tbody>

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.

Awesome, that did the trick. I was close just missing the order of the If you had. Thanks!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.