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.

Client	Campaign	Sent	Unique Opens	Opens rate
Product1	Campaign1	20	14	70.00%
Product1	Campaign2	18	11	61.11%
Product1	Campaign3	10	8	80.00%
Product1	Campaign4	9	6	66.67%
Product1	Campaign5	5172	2140	41.86%
Product1	Campaign6	129	85	65.89%
Product1	Campaign7	5	3	60.00%
Product1	Campaign8	13028	7061	55.16%
Product1	Campaign9	9407	5234	56.69%
Product1	Campaign10	1072	536	52.09%

<tbody>
</tbody>

Result:

Client	Sheet	Min Open	Max Open
Client Name1	Product1	41.86%	56.69%
Client Name2	Product2

<tbody>
</tbody>

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.

Client	Campaign	Sent	Unique Opens	Opens rate
Product1	Campaign1	20	14	70.00%
Product1	Campaign2	18	11	61.11%
Product1	Campaign3	10	8	80.00%
Product1	Campaign4	9	6	66.67%
Product1	Campaign5	5172	2140	41.86%
Product1	Campaign6	129	85	65.89%
Product1	Campaign7	5	3	60.00%
Product1	Campaign8	13028	7061	55.16%
Product1	Campaign9	9407	5234	56.69%
Product1	Campaign10	1072	536	52.09%

<tbody>
</tbody>

Result:

Client	Sheet	Min Open	Max Open
Client Name1	Product1	41.86%	56.69%
Client Name2	Product2

<tbody>
</tbody>

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.