I need some help with an IFAnd formula; real confussed.

[susiesc]

I added the column water bill and need to enter formula to calculate the water bill based on these rules:

The billing rate varies based on the type of customer (Cust type column).
The billing rate is $3, $2, or $1.50 per thousand gallons billed, depending on the
customer type (Look Up table is in Cells B2:C5 of the billing rate worksheet).
The example is, a comercial customer using 75,000 gallons has a water bill of $225 (75 x $3), whereas a government
customer using 100,000 gallons pays $150 (100 x $1.50). A commercial customer using 15,000 gallons has a water bill of $0.

Can you help me with this formula? This is so confusing.

Customer Name Cust TypeBill WaivedTaxable Billing YearGal UsedGal BilledWater Bill
Red Lake Chapter NPROFIT No Yes 2013 108,300 108,300
Amity Church NPROFIT Yes No 2013 55,160 0
Church of Christ NPROFIT Yes No 2013 32,430 0


Billing Rate worksheet:

Type Bill Rate
COM $3.00
NPROFIT $2.00
GOV $1.50