Returnign largst or smallest value from a subset of values

[nightracer]

Hi there

Am hoping someone could point me in the right direction with a quick one.

I have a list as below, and am looking to return the smallest or largest value by day:

A B
Mon 154
Tue 192
Wed 216
Thu 147
Fri 126
Sat 98
Sun 48
Mon 52
Tue 69
Wed 210
Thu 99
Fri 137
Sat 104
Sun 146
Mon 97
Tue 184
Wed 39
Thu 172
Fri 184
Sat 90
Sun 75
Mon 163
Tue 92
Wed 87
Thu 141
Fri 78
Sat 139
Sun 52

Is there a formula that will look at this list for just Monday's for example?
So for example in D1 I put 'Mon' and have E1 = 52 (small) and F1 = 163 (large)

Many thanks for any help.

 

[Rick Rothstein]

Try these...

For Minimum: =MIN(IF(A$1:A$28=D1,B$1:B$28))

For Maximum: =MAX(IF(A$1:A$28=D1,B$1:B$28))

Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Eric W]

Try:

	A	B	C	D	E
1	A	B		Day	Min
2	Mon	154		Mon	52
3	Tue	192		Tue	69
4	Wed	216		Wed	39
5	Thu	147		Thu	99
6	Fri	126		Fri	78
7	Sat	98
8	Sun	48
9	Mon	52
10	Tue	69
11	Wed	210
12	Thu	99
13	Fri	137
14	Sat	104
15	Sun	146
16	Mon	97
17	Tue	184
18	Wed	39
19	Thu	172
20	Fri	184
21	Sat	90
22	Sun	75
23	Mon	163
24	Tue	92
25	Wed	87
26	Thu	141
27	Fri	78
28	Sat	139
29	Sun	52

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula E2 {=MIN(IF($A$2:$A$29=D2,$B$2:$B$29))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Or if you have Excel 365:

=MINIFS(B:B,A:A,D2)

 

[nightracer]

Fantastic, thank you both Rick and Eric, that works perfectly!

Thank you
N