Get minimum sum

[Demon4231]

I am trying to create a formula that would return the smallest sum value for each color.

For example, in the table below amount 01 and amount 02 are added together. The results I am looking for would be Green 6, Blue 8, and Red 11.

Color	Amount 01	Amount 02
Green	1	5
Blue	6	2
Red	5	6
Green	8	2
Red	7	7

I am trying to avoid having a total column in each row or using macros. Is there a way to do this with just formulas?

[XLent]

assuming XL2003 (per profile), one option might be:

=INDEX($A:$A,ROUND(MOD(MIN(INDEX(SUBTOTAL(9,OFFSET($B$2:$C$2,ROW($B$2:$B$6)-ROW($B$2),0))+ROW($B$2:$B$6)/10^7,0)),1)*10^7,0))
where A2:C6 hold your values

the above would return "Green", if you were to change Green 1 to 10 then it would return "Blue" (8).

edit: I realise, belatedly, you're looking to return the unique colour list with lowest total - will revert, if others do not do so first.

[XLent]

so, apologies -- 2nd attempt -- again, this assumes XL2003 constraints

F2: =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX($A$2:$A$6,MATCH(1,INDEX(($A$2:$A$6<>"")*ISNA(MATCH($A$2:$A$6,F$1:F1,0)),0),0))))
copied down

G2: =IF($F2="","",MIN(INDEX(SUBTOTAL(9,OFFSET($B$2:$C$2,ROW($B$2:$B$6)-ROW($B$2),0))+9.99E+307*($A$2:$A$6<>$F2),0)))
copied down

[Demon4231]

I may have explained what I was looking for wrong, I want to show all of the colors min values (not the name itself) like in the table below.

Red	11
Green	6
Blue	8

I think I can use that to get me started though, thank you.


I am using XL2016 (haven't updated my profile in a while).

[XLent]

so, the formulae in post #3 should work but, using XL2016, you can swap out some of the older approaches c/o later functions, e.g.

F2: =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$6)/ISNA(MATCH($A$2:$A$6,$F$1:$F1,0)),1)),"")
copied down

G2: =IF($F2="","",AGGREGATE(15,6,SUBTOTAL(9,OFFSET($B$2:$C$2,ROW($B$2:$B$6)-ROW($B$2),0))/($A$2:$A$6=$F2),1))
copied down

[Demon4231]

That works perfectly, thank you!