Get the max and min based on certain criteria

[MagsinoAS13]

Please help me look for a formula to get the Max and Min value based on given criteria.

Client	Campaign Name	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: The sent must be higher than 1000 to get the Max and Min Open Rate. In the example above, there are 4 rows with more than 1000 sent. So the Max Open Rate is 56.69% and Min Open Rate is 41.86. If the Sent column has no value higher than 1000, just get the normal Max and Min. In this case, Max is 80% and Min is 41.86%.

Any help is very much appreciated.
Thanks.

 

[Special-K99]

There may be a better way

=IF(COUNTIF(C2:C11,">1000"),MAX(IF(C2:C11>1000,E2:E11)),MAX(E2:E11))
Array formula, use Ctrl-Shift-Enter
=IF(COUNTIF(C2:C11,">1000"),MIN(IF(C2:C11>1000,E2:E11)),MIN(E2:E11))
Array formula, use Ctrl-Shift-Enter

 

[Peter_SSs]

I don't understand the first two columns of your result section.
- Client Name 1 and Client name 2 do not appear in your sample data, and
- The second column has a heading of Sheet but the values below that appear to come from the Client column in your data

However, see if these standard-entry formulas do what you want

Excel Workbook
	A	B	C	D	E
1	Client	Campaign Name	Sent	Unique Opens	Opens rate
2	Product1	Campaign1	20	14	70.00%
3	Product1	Campaign2	18	11	61.11%
4	Product1	Campaign3	10	8	80.00%
5	Product1	Campaign4	9	6	66.67%
6	Product1	Campaign5	5172	2140	41.86%
7	Product1	Campaign6	129	85	65.89%
8	Product1	Campaign7	5	3	60.00%
9	Product1	Campaign8	13028	7061	55.16%
10	Product1	Campaign9	9407	5234	56.69%
11	Product1	Campaign10	1072	536	52.09%
12
13
14	RESULT
15	Client	Sheet	Min Open	Max Open
16	Client Name1	Product1	41.86%	56.69%
17	Client Name2	Product2
Min Max

 

[MagsinoAS13]

There may be a better way

=IF(COUNTIF(C2:C11,">1000"),MAX(IF(C2:C11>1000,E2:E11)),MAX(E2:E11))
Array formula, use Ctrl-Shift-Enter
=IF(COUNTIF(C2:C11,">1000"),MIN(IF(C2:C11>1000,E2:E11)),MIN(E2:E11))
Array formula, use Ctrl-Shift-Enter

Great, this is what I need. Thanks.

 

[jtakw]

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