Formula Parse Error: IFS referring to a subtotal cell

Occasional Visitor

I'm trying to use an IFS function to tell the document user who signs off the order based on the subtotal of another cell:

 

Here is my current attempt:

=IFS(G25>20000,"Email Budget Holder, Recommended by FARCO, For Authorisation by the Trust Board (plus three quotes)", G25>10000, "Email Budget Holder, Endorsement by CEO, Authorised by FARCO (plus three quotes)", G25>5000, "Spend Request Needed. Email Budget Holder and CEO (plus three quotes)", G25>3000 "Budget Holder and CFO/CEO (plus three quotes)", G25>1000,"EHT or COO (plus two quotes)", G25>500, "EHT or COO", G25<500, "Subject Lead or Budget Holder")

 

Here is the sum in cell G25: 

=SUM(G15:G24)

 

Currently Returning:

#ERROR!

Formula Parse Error

2 Replies

@KirstyExcel 

Hi

 

It is generally not good practice to use complex IFS statements as they are difficult to decipher and maintain. You might want to consider using a VLOOKUP with the messages in a table. See example below. The VLOOKUP looks for a value approximate to the one in the table and retrieves the associated message. I hope this helps.  Peter

 

 

peteryac60_0-1667822573652.png

 

@KirstyExcel 

In your IFS there is only 1 comma missing after "G25>3000" (highlighted in the formula below).

 

=IFS(G25>20000,"Email Budget Holder, Recommended by FARCO, For Authorisation by the Trust Board (plus three quotes)", G25>10000, "Email Budget Holder, Endorsement by CEO, Authorised by FARCO (plus three quotes)", G25>5000, "Spend Request Needed. Email Budget Holder and CEO (plus three quotes)", G25>3000 , "Budget Holder and CFO/CEO (plus three quotes)", G25>1000,"EHT or COO (plus two quotes)", G25>500, "EHT or COO", G25<500, "Subject Lead or Budget Holder")

 

ifs.JPG

However it's easier to apply a lookup formula which has already been suggested.