I am trying to write a formula with 3 possible outcomes, but I keep getting the “There’s a problem with this formula” error message, and I can’t figure out why.  Here is what I have:

=IF(C18=E91:E95,K18+K32+K34-K36,IF(AND(C18=E138:E143,MAX(SUM(K32:K35)-K36,8500),IF(AND(C18=E86:E135,MAX(SUM(K32:K35)-K36,7000),””)))))

This is for an invoice, and C18 will equal a service code.  There are multiple service codes, but there are 3 ways to find the total for the invoice.

1.  If service code is in E91:E95, then it should total this way K18+K32+K34-K36

2.  If service code is in E138:E143, then we need to add K32:K35 and subtract K36, but the minimum total it can be is $8,500.00, if over this amount then use the total.

3.  If service code is in E86:E135, then we need to add K32:K35 and subtract K36, but then minimum total it can be is $7,000.00, if over this amount then use the total.

​I am trying to write a formula with 3 possible outcomes, but I keep getting the “There’s a problem with this formula” error message, and I can’t figure out why.  Here is what I have: =IF(C18=E91:E95,K18+K32+K34-K36,IF(AND(C18=E138:E143,MAX(SUM(K32:K35)-K36,8500),IF(AND(C18=E86:E135,MAX(SUM(K32:K35)-K36,7000),””))))) This is for an invoice, and C18 will equal a service code.  There are multiple service codes, but there are 3 ways to find the total for the invoice. 1.  If service code is in E91:E95, then it should total this way K18+K32+K34-K362.  If service code is in E138:E143, then we need to add K32:K35 and subtract K36, but the minimum total it can be is $8,500.00, if over this amount then use the total.3.  If service code is in E86:E135, then we need to add K32:K35 and subtract K36, but then minimum total it can be is $7,000.00, if over this amount then use the total.     Read More

​