What is the best way to resolve or to simplify the following excel functions using excel 2020 or greater

Yes. For Excel 2020 or later, the clearest simplification for the first formula is IFS, because it replaces multiple nested IF statements and is easier to read.

A direct rewrite of the scoring logic is:

=IFS(
B2=0,0,
B2>I2,4,
B2=I2,2,
TRUE,0
)
+IFS(
C2=0,0,
C2>J2,4,
C2=J2,2,
TRUE,0
)
+IFS(
D2=0,0,
D2>K2,4,
D2=K2,2,
TRUE,0
)
+IFS(
E2=0,0,
E2>L2,4,
E2=L2,2,
TRUE,0
)

This keeps the same result pattern:

• 0 if the source cell is 0
• 4 if the source cell is greater than the comparison cell
• 2 if equal
• 0 otherwise

IFS is designed to replace nested IF statements and returns the value for the first TRUE condition. A final TRUE,0 acts as the default result.

For the second formula, if the goal is to total matching values and then show the difference from 600, a simpler form is:

=SUMIFS($E18:$L18,$A18:$H18,"*COFIELD*")-600

That returns:

• a positive number if the total is greater than 600
• a negative number if the total is less than 600
• 0 if the total equals 600

If the total is 599, the result is -1. If the total is 600, the result is 0. If the total is 615, the result is 15.

If only the absolute difference is needed, regardless of plus or minus, use:

=ABS(SUMIFS($E18:$L18,$A18:$H18,"*COFIELD*")-600)

Also note for SUMIFS:

• text criteria such as "*COFIELD*" should be in quotation marks
• the sum_range is the first argument in SUMIFS

• IFS function
• IF function – nested formulas and avoiding pitfalls
• SUMIFS function
• Sum values based on multiple conditions