Apportionment Formula

[HangMan]

I have a worksheet containing 5 columns and amounts in a seperate column that need to be apportioned to each column based on a number of rules. I need to write a single formula that applies the apportionments proportionally based on these rules.

To try and simply things, I've attached a much simplified version, showing just one column, the rules that apply and the amounts in column E that need to be proportionally apportioned to Column B. The formula would need to sit in coumn C.

The best I've come up with so far, based on the actual columns in the original worksheet (which won't apply to the sample directly) is:

=IFERROR(IF(AND($AS3<=0,$AY3<=0,$BE3<=0,$BK3<=0,$BR3<=0),$BX3/5,IF($AS3>0,$AS3/(SUMIF($AS3:$AS3,">0",$AS3:$AS3)+SUMIF($AY3:$AY3,">0",$AY3:$AY3)+SUMIF($BE3:$BE3,">0",$BE3:$BE3)+SUMIF($BK3:$BK3,">0",$BK3:$BK3)+SUMIF($BR3:$BR3,">0",$BR3:$BR3))*$BX3,0)),0)

but I can't figure out how to apportion the figures in column E of the attached sample, to meet 'all'the rules. I've added some notes to the attached worksheet to make it easier to see what I'm trying to achieve. My half working formula above, is to show my approach so far and is based on having all 5 columns, which on the original would be columns AS, AY, BE, BK and BR. As I say, this is for reference only.

I'm totally stumped on this one and would be grateful for any help to resolve it.

Many thanks