Tier pricing for data

Hello all,

I'm driving crazy trying to calculate how much will my company pay for a service that charges a fixed amount per invoice plus a variable amount per amount of data. For the fixed amount it's fine, since it's an amount that will be automatically payed every year. For the variable amount, though, it's much more complicated.

I have estimated the amount of data that we will be sending to this company per trimester. The pricing for this data from them is the following:

- The first 100GB of data of every month are free.
- From 101 to 2500 costs 10 EUR/GB - Tier 1
- From 2501 to 5000 costs 7.5 EUR/GB - Tier 2
- From 5001 to 10001 costs 5 EUR/GB - Tier 3
- From 10001 to 25000 costs 2.5 EUR/GB - Tier 4
- From 25001 to 50000 costs 1.5 EUR/GB - Tier 5
- From 50000+ costs 1 EUR/GB - Tier 6

My challenge here is the following: given the amount of data that I will upload every trimester, which I have disposed in an array, I want to know how much of it goes to each one of the tiers, taking into account that the tiers get reset to 0 every time a new invoice is payed (an invoice lasts 1 year). Once I know the amount of data that goes to every tier in each one of the trimesters, the rest is just multiplying it by the price of the tier.

I tried first with IFs (I was using 6 or 7 in the same formula, did not work well), then with SUMPRODUCT for calculating the yearly costs and then do an average per trimester, but did not work well neither.

Can anyone help me with this? Or suggest me the appropriate tool to use? Thank you very much.

Or try:

=SUMPRODUCT((A2>$H$4:$H$9)*(A2-$H$4:$H$9)*($I$4:$I$9-$I$3:$I$8))