multiple sums in 1 cell

Hi

I need to work out a fee dependant on size of holding (more holding = lower fee) - size of 0-50mill = 0.50%, then from 50-100mill = 0.40%, then over 100mill = 0.30%. I have attached an excel sheet with rows 1 to 24 showing the workings now. I want to reduce all this down to 3 columns (currently 9) which is showing in rows 28 to 51.

I want the formula in column C of the second block to basically work out what columns C to I do in the top block. So the formula would be, for example, 0 to 50,000,000 of B30 * 0.50% plus 50,000,000.00 to 100,000,000.00 of B30 * 0.40% plus anyone over 100,000,000.00 * 0.30% (the result of each of these mini sums needs to be divided by 366 then multiplied by the difference in days between, for example, date in A29 and date in A30).

Can anyone help?

Pretty coplicated, but you can try:

Formula:

=SUM(IF(B30<=50000000,((B30*0.005)/366)*(A30-A29),((50000000*0.005)/366)*(A30-A29)),IF(AND(B30>50000000,B30<=100000000),(((B30-50000000)*0.004)/366)*(A30-A29),IF(B30>100000000,((50000000*0.004)/366)*(A30-A29),0)),IF(B30>100000000,(((B30-100000000)*0.003)/366)*(A30-A29),0))

put that in cell D30 of your test document you posted.