Running balance with a condition

[mondayblues]

Hi, i'm trying to get an an amount allocated across a few lots based on its rank. I managed to do this with the formula below (table starts at A1). For apples =MIN(C2,$B$10-SUMIF($E$2:$E$4,"<"&E2,$D$2:$D$4)) For bananas =MIN(C5,$B$11-SUMIF($E$5:$E$7,"<"&E5,$D$5:$D$7)) Now instead of writing 2 separate formulas, i'm trying to expand this to do the same based on its group as well. This is where i need help. Group Lot Units Allocation Rank Apples 1.1 29200 29062 1 Apples 1.2 2000 0 2 Apples 1.3 3000 0 3 Bananas 2.1 2778 1136 3 Bananas 2.2 2211 2211 1 Bananas 2.3 5942 5942 2 Allocation Apples 29062 Bananas 9289 Any suggestions?