excelif-statementexcel-formulamin

How do I get the minimum value of one column base on conditions met from other columns in Excel?

I have Table A and I want to create two new columns to get table B. I am trying to create a formula where I am only calculating the min or max of previous records for each person. Is there a formula that I can use to automatically fill in for the rest of the records? I have tried this formula but I received an error.

= MIN(IF(AND(C:C=$C2, A:A<$A2),D:D))

TABLE A:

A B C D
1 SES DATE NAME SCORE
2 1 9/4/23 Alice 165
3 1 9/4/23 Bob 224
4 1 9/4/23 Tina 170
5 2 10/7/23 Alice 160
6 2 10/7/23 Bob 218
7 2 10/7/23 Tina 167
8 3 11/4/23 Alice 155
9 3 11/4/23 Bob 220
10 3 11/4/23 Tina 165
11 4 12/2/23 Alice 150
12 4 12/2/23 Bob 221
13 4 12/2/23 Tina 168

TABLE B:

A B C D E F
1 SES DATE NAME SCORE MIN MAX
2 1 9/4/23 Alice 165 NA NA
3 1 9/4/23 Bob 224 NA NA
4 1 9/4/23 Tina 170 NA NA
5 2 10/7/23 Alice 160 165 165
6 2 10/7/23 Bob 218 224 224
7 2 10/7/23 Tina 167 170 170
8 3 11/4/23 Alice 155 160 165
9 3 11/4/23 Bob 220 218 224
10 3 11/4/23 Tina 165 167 170
11 4 12/2/23 Alice 150 155 165
12 4 12/2/23 Bob 221 218 224
13 4 12/2/23 Tina 168 165 170
Solution

  • For min in E2 and dragged down:

    =MINIFS($D$1:D1,$C$1:C1,C2)

    For max in F2 and dragged down:

    =MAXIFS($D$1:D1,$C$1:C1,C2)

    Result:

    enter image description here