Excel GROUP MINIMUM VARIABLE

Question

    ID  Value   Valid   MAX
Group3  2   NO  8
Group3  8   YES 8
Group1  4   YES 4
Group4  2   NO  1
Group4  3   NO  1
Group4  1   YES 1
Group1  2   YES 4
Group1  6   NO  4
Group3  4   YES 8
Group2  1   YES 3
Group3  7   NO  8
Group2  3   YES 3
Group5  7   NO  1

I have 'ID' 'VALUE' and can make 'MAX' by =MAX(IF(A:A=A2,B:B)) BUT I wish to make a variable 'ISMAX' that equals to 'a' if the row is the ID max or 'b' if it is not AND the only values that count are ones where "YES" is in "VALID" column.

Answer 1

Try =IF(MAXIFS(B:B,A:A,A2,C:C,"YES")=B2,"a","b")

For Group 1 4 is the max valid value. Otherwise (excluding the valid=yes-check) it would be 6.