Forum Discussion

erin-5304's avatar
erin-5304
Brass Contributor
Oct 30, 2024

Help on error message "There's a problem with this formula"

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.

 

 

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 30, 2024

    erin-5304 

    Try this:

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

    Remark: E91:E95 and E86:E135 overlap. Is that really what you intended? Shouldn't E86:E135 be E96:E135 or something like that?

Resources