Complex if formula help

Hi,

I am trying to create a formula that will only sum up to a certain threshold in a certain category. I have attached a sample spreadsheet that shows 5 different fees with max credits. There are cells highlighted yellow where the formula would go. I need the formula to sum the amount shown for that column up to the max fee for the category.

Is there a formula I can use to accomplish this?


Thank you,

Bilich

The orange highlight shows the expected answers.

Basically, the max fee is the maximum amount that can be used in that corresponding row (ie. Fee 1 can only have 100 credit so the formula should only add the amount in column C & D. Column E pushes it over the 100 limit). I want this formulated so I can adjust the numbers and the credit amount will automatically calculate.

Does that make sense?


Thank you,

or smth like that
=SUM(IF(MMULT(--$B$5:B9,ROW($A$1:INDEX($A:$A,B$4))^0)<$B$17:$B$21,MMULT(--$B$5:B9,ROW($A$1:INDEX($A:$A,B$4))^0),$B$17:$B$21)) CSE

Thank you bebo021999 and tim201110. This was very helpful!