Get the Max and Min Value based on criteria

[MagsinoAS13]

Please help me look for a formula to get the maximum and minimum value based on certain criteria.
Thanks.

[TABLE="width: 419"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Campaign[/TD]
[TD]Sent[/TD]
[TD]Unique Opens[/TD]
[TD]Opens rate[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign1[/TD]
[TD]20[/TD]
[TD]14[/TD]
[TD]70.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign2[/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD]61.11%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign3[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]80.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign4[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]66.67%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign5[/TD]
[TD]5172[/TD]
[TD]2140[/TD]
[TD]41.86%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign6[/TD]
[TD]129[/TD]
[TD]85[/TD]
[TD]65.89%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign7[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]60.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign8[/TD]
[TD]13028[/TD]
[TD]7061[/TD]
[TD]55.16%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign9[/TD]
[TD]9407[/TD]
[TD]5234[/TD]
[TD]56.69%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign10[/TD]
[TD]1072[/TD]
[TD]536[/TD]
[TD]52.09%[/TD]
[/TR]
</tbody>[/TABLE]

Result:
[TABLE="width: 493"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Sheet[/TD]
[TD]Min Open[/TD]
[TD]Max Open[/TD]
[/TR]
[TR]
[TD]Client Name1[/TD]
[TD]Product1[/TD]
[TD]41.86%[/TD]
[TD]56.69%[/TD]
[/TR]
[TR]
[TD]Client Name2[/TD]
[TD]Product2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Criteria: Sent value should be more than 1,000 then I need to get the max and min value. In this case, we have 4 rows with more than 1000. And the Min is 41.86% and 56.69%.
However, If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

Thank you so much.

 

[Legacy 143009]

Please help me look for a formula to get the maximum and minimum value based on certain criteria.
Thanks.

[TABLE="width: 419"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Campaign[/TD]
[TD]Sent[/TD]
[TD]Unique Opens[/TD]
[TD]Opens rate[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign1[/TD]
[TD]20[/TD]
[TD]14[/TD]
[TD]70.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign2[/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD]61.11%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign3[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]80.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign4[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]66.67%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign5[/TD]
[TD]5172[/TD]
[TD]2140[/TD]
[TD]41.86%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign6[/TD]
[TD]129[/TD]
[TD]85[/TD]
[TD]65.89%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign7[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]60.00%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign8[/TD]
[TD]13028[/TD]
[TD]7061[/TD]
[TD]55.16%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign9[/TD]
[TD]9407[/TD]
[TD]5234[/TD]
[TD]56.69%[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Campaign10[/TD]
[TD]1072[/TD]
[TD]536[/TD]
[TD]52.09%[/TD]
[/TR]
</tbody>[/TABLE]

Result:
[TABLE="width: 493"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Sheet[/TD]
[TD]Min Open[/TD]
[TD]Max Open[/TD]
[/TR]
[TR]
[TD]Client Name1[/TD]
[TD]Product1[/TD]
[TD]41.86%[/TD]
[TD]56.69%[/TD]
[/TR]
[TR]
[TD]Client Name2[/TD]
[TD]Product2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Criteria: Sent value should be more than 1,000 then I need to get the max and min value. In this case, we have 4 rows with more than 1000. And the Min is 41.86% and 56.69%.
However, If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

Thank you so much.

What if there is only one "more then 1000" value and the rest are less? Should it be considered as Max or Min?

 

[ttray33y]

for Min try:

=MIN(IF(C2:C11>=1000,E2:E11))

=MAX(IF(C2:C11>=1000,E2:E11))

paste these formula on there respective destinations and CTRL+SHIFT Enter as this is an array formula.

note: just remove the = sign if you don't want 1000 to be counted.

 

[MagsinoAS13]

Yes, this will work. However, I have a second condition.
If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

 

[MagsinoAS13]

Yes, that is correct. And take note of this condition....If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

What if there is only one "more then 1000" value and the rest are less? Should it be considered as Max or Min?

 

[Legacy 143009]

This

for Min try:

=MIN(IF(C2:C11>=1000,E2:E11))

=MAX(IF(C2:C11>=1000,E2:E11))

does not meet this

However, If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

condition.

 

[Legacy 143009]

Yes, that is correct. And take note of this condition....If the sent has no value that is more than 1000, then we just get the Max and Min values, 80.00% and 41.86%.

Sorry, I am not sure that I asked the question correctly. What happens if there is only one value more than 1000?

 

[MagsinoAS13]

Sorry, I am not sure that I asked the question correctly. What happens if there is only one value more than 1000?

I take that as the Max Open If there is only one value more than 1000. Then for the Min Open, we just get the min value out of all.

 

[jtakw]

Double posted: https://www.mrexcel.com/forum/excel-questions/1062505-get-max-min-based-certain-criteria.html

 

[Peter_SSs]

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

I have closed this one as the other one has a response that you have accepted, though I am not sure that agrees with what you have said in post #8 here.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.