Complicated use of IF formula

I'm managing calculation of water bills where different states have different water tariff & would really really appreciate if anyone can help me on this scenario.

State A
1st 50L usage charged at 1.00 per L
Usage of 51L-100L charged at 1.20 per L
Usage of 101L-150L charged at 1.40 per L
Usage of 151L and above charged at 1.50 per L
Eg. Usage of 125L would be charged at (50L x 1.00) + (next 50L x 1.20) + (last 25L at 1.40), giving 145.00

State B
Flat rate 2.00 per L

State C
1st 30L usage charged at 1.50 per L
Usage of 30L and above charged at 1.70 per L

I would have :
Cell A : Name of Company
Cell B : State (eg. State A)
Cell C : Consumption (eg. 100L)
Cell D : Charges (To insert formula here)

I tried to ponder around but I could only make it by using IF formulae which is very very long.

Tariff Sample.xlsx

Many thanks in advance if anyone could help me with this!

Can you upload an example workbook with some comments for us to try??

B3 is the Consumption
B4 are the calculated charges

NOTE:
With this equation once the usage exceeds the threshold, all charges are at the new rate. For example, in State C if you use 31L, all 31L will be charged at 1.70 per L.

=================================================
If this is not what you had in mind, the formula needs to be changed to this:
=IF(B2="A",IF(B3<=50,B3*1,IF(B3<=100,(B3-50)*1.2+50,IF(B3<=150,(B3-100)*1.4+110,(B3-15)*1.5+180))),IF(B2="B",B3*2,IF(B2="C",IF(B3<=30,B3*1.5,(B3-30)*1.7+45))))

Attached is a simple spreadsheet for both approaches. Sample 1A is for the 2nd formula
============================
A better approach would be to set up 3 lookup tables

The better approach is several lookup tables, one for each state. This way the formula can be shortened.

More work on your part initially

SUMPRODUCT may also work but others can help with that

Originally Posted by K m

The better approach is several lookup tables, one for each state. This way the formula can be shortened.

More work on your part initially

SUMPRODUCT may also work but others can help with that

And of course, we need a sample workbook for this.

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.