create a formula that covers several variables/possabilities

I need a formula that allows for all of the variables to be taken into account


Sum A4:Ae4 within this range there could be just X's, or X's, numbers(multiples of) 20, and/or F,H,R,S or E
X=0 0="C"
F="Elim"
H="Elim"
R="Ret"
E="Elim"
S="SCR"

I've tried a few ideas but nothing seems to work. Any help would be greatly appreciated

I have attached a copy of worksheet, which hopefully explains a lot clearer what is required.

in Column -AG ,I've entered the required outcome for each of the possible scenarios

the cells coloured green are my workings only.

In cell AF18 is a copy of a formula I was trying to create

Column AH (Italics) contains legend of meanings.

How's this for you?

=IFERROR(CHOOSE(MATCH(TRUE,INDEX(COUNTIF(A4:AE4,{"E","F","H","R","S"})>0,0),0),"Elim","Elim","Elim","Ret","SCR"),IF(SUM(A4:AE4)=0,"C",SUM(A4:AE4)))

Hi Andrew
Thanks for your quick response. I.ve just completed a test run of your solution. All works well except for row 14, this should show "SCR", but shows "C". Is there a simple solution to fix this hiccup?

Yes, I was a bit puzzled by row 14.

Your original post in this thread says that SCR is the result if the row contains "S", but then SCR isn't mentioned on the sheet, but is the expected value for row 14, which is blank.

Could you clarify what the rule is, please?

Andrew
Sorry about that. What I can do is transfer from my data sheet "S" into column A, no other entry would be made against this rider, therefore the result should be SCR. Just tried running this solution, all work great.
Thank you very much. I will try and read up on the various parts of your formula and hopefully learn how it works.