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
A	B	Day	Min
Mon	Mon
Tue	Tue
Wed	Wed
Thu	Thu
Fri	Fri
Sat
Sun
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Mon
Tue
Wed
Thu
Fri
Sat
Sun

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]154[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]192[/TD]
[TD="align: right"][/TD]

[TD="align: right"]69[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]216[/TD]
[TD="align: right"][/TD]

[TD="align: right"]39[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]147[/TD]
[TD="align: right"][/TD]

[TD="align: right"]99[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]126[/TD]
[TD="align: right"][/TD]

[TD="align: right"]78[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]210[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]137[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]104[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]146[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]97[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]184[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]172[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]184[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]163[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]92[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]141[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]139[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]{=MIN(IF($A$2:$A$29=D2,$B$2:$B$29))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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