If two conditions are met, return the smallest value in a range

[dportnoy]

Hi.

I have the data below.

My goal is to return the smallest value in the second column if
1. The first column says Student## AND
2. The third column has a 1 in it.

For example, if the range in the first column says "student81" and out of the remaining rows in the array that have both "student81" and the number "1" in the third column, I would to create a formula that will return the smallest value in the second column when those two conditions are met.

So, if student81 is selected as shown in the picture below, the formula would return the number 5 since its the smallest value when 1 is in the third column.

Thank you so much!!!

 

[etaf]

MINIFS()
should work
=MINIFS($C$2:$C$22,$D$2:$D$22,1,$A$2:$A$22,G3)

you have 2016 version so
=MIN(IF(($A$1:$A$22=G3)*($D$1:$D$22=1),$C$1:$C$22))
entered as an array - control + shift + enter

Book1
	A	B	C	D	E	F	G	H	I
1
2	Student 1	1	100	1				MINIFS	IF(MIN
3	Student 2	2	200	2			student 1	100	100
4	Student 3		300	1			student 5	300	300
5	Student 4		100	3
6	Student 5		200	4
7	Student 6		300	1
8	Student 7		200	2
9	Student 1		300	5
10	Student 2		300	1
11	Student 3		300	2
12	Student 4		300	1
13	Student 5		300	1
14	Student 6		400	4
15	Student 7		400	1
16	Student 1		400	2
17	Student 2		400	5
18	Student 3		500	1
19	Student 4		500	1
20	Student 5		500	2
21	Student 6		500	5
22	Student 7		600	1
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=MINIFS($C$2:$C$22,$D$2:$D$22,1,$A$2:$A$22,G3)
I3:I4	I3	=MIN(IF(($A$1:$A$22=G3)*($D$1:$D$22=1),$C$1:$C$22))

 

[dportnoy]

MINIFS()
should work
=MINIFS($C$2:$C$22,$D$2:$D$22,1,$A$2:$A$22,G3)

you have 2016 version so
=MIN(IF(($A$1:$A$22=G3)*($D$1:$D$22=1),$C$1:$C$22))
entered as an array - control + shift + enter

Book1
	A	B	C	D	E	F	G	H	I
1
2	Student 1	1	100	1				MINIFS	IF(MIN
3	Student 2	2	200	2			student 1	100	100
4	Student 3		300	1			student 5	300	300
5	Student 4		100	3
6	Student 5		200	4
7	Student 6		300	1
8	Student 7		200	2
9	Student 1		300	5
10	Student 2		300	1
11	Student 3		300	2
12	Student 4		300	1
13	Student 5		300	1
14	Student 6		400	4
15	Student 7		400	1
16	Student 1		400	2
17	Student 2		400	5
18	Student 3		500	1
19	Student 4		500	1
20	Student 5		500	2
21	Student 6		500	5
22	Student 7		600	1
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=MINIFS($C$2:$C$22,$D$2:$D$22,1,$A$2:$A$22,G3)
I3:I4	I3	=MIN(IF(($A$1:$A$22=G3)*($D$1:$D$22=1),$C$1:$C$22))

Thank you! It worked. Is there anyway to get around the Control + Shift + Enter part so the spreadsheet is automatically populated?

Thank you again!

 

[etaf]

Is there anyway to get around the Control + Shift + Enter part so the spreadsheet is automatically populated?

Not sure what you mean,
control + Shift + Enter
changes the formula to an Array formula and adds { }
Then it acts automatically like any function, providing the settings are set to automatic calculation and NOT set to manual

 

[dportnoy]

Thank you! It worked. Is there anyway to get around the Control + Shift + Enter part so the spreadsheet is automatically populated?

Thank you again!

Nevermind, yes that's my bad. I didn't actually attempt populating it down and assumed the #VALUE error would just pop up. Thank you so much again. This was very helpful.

 

[etaf]

if you want to avoid an Error showing
then you can use an IFERROR()

=IFERROR( MIN(IF(($A$1:$A$22=G3)*($D$1:$D$22=1),$C$1:$C$22)) , "" )
will put a blank in the cell if there is any error
#N/A , #VALUE etc etc