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.

[TABLE="width: 446"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Campaign Name[/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: 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