SUMPRODUCTIF with date in a table?

Untitled.jpg

Here is what the spreadsheet looks like. I've deleted some columns for simplicity. In which case, the formula would read
=SUMPRODUCT(--(D12/$E$1)>$C$5:$C$9;((D12/$E$1)-$C$5:$C$9);$J$5:$J$9)

Here it is! Thanks again.

My apologies. Reading my original post I can see that my problem isn't very clear.

What I need is a formula that will calculate the management fees, according to the net premium level, the year and the related percentage. The fee structure works like marginal income tax rates(meaning, the first 900 are charged a 55% fee, the next up to 1800 at 30%, and so forth). This is why I created column J with the marginal rates for year 1.

The manual way of doing it would use this formula : =(($D$5*$E$1)-D12)*$E$6+($D$5*$E$1)*$E$5; and would require that you manually change the formula every time the year changes, or the premium changes level according to the table. The idea is to develop a formula that will account for the premium level and year on its own.

The condition in the SUMPRODUCT formula works, but it doesn't provide a result, and it also doesn't account for the year.

In year 1 the results should be 104,11, year 2 26,15, year 3 8,71, year 4 5,35, year 5 5,1, year 6 4,96, year 7+ 0.

Also note, in the example provided the customer is paying quarterly, but the fee schedule is based on annual payments which is why E1 is in order to account for different payment structures (Annual, semi-annual, quartly).

I hope that makes things clearer.

Is this ok?

Thanks for your help!