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

Question

Can I use the IFS function or LET, SUMIFS?

=SUM(IF($B$2=0,0,IF($B$2>$I$2,4,IF($B$2=$I$2,2))+IF($C$2=0,0,IF($C$2>$J$2,4,IF($C$2=$J$2,2))+IF($D$2=0,0,IF($D$2>$K$2,4,IF($D$2=$K$2,2))+IF($E$2=0,0,IF($E$2>$L$2,4,IF($E$2=$L$2,2)))))))

If I have a value of 599 or greater than 600, how can I use the +- to display the value difference in a cell or to show a 0 value if equal? sum total 599 greater or less, my default value is 600

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

Thank You

Answer 1

Unless the poster clarifies, I believe they want zero, not 12

IF(B2=0,0, ... the end!

Answer 2

Try this formula:

=SUMPRODUCT((B2:E2 > 0) * ((B2:E2 = I2:L2) * 2 + (B2:E2 > I2:L2) * 4))

If you are using Excel 2021 or newer versions, then instead of the SUMPRODUCT function in the formula, you can simply use the SUM function. So replace the SUMPRODUCT function in the formula with the SUM function.

Regarding the second problem, you should use the formula already suggested by AI answer, which is this:

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

If you want the result to come out with a plus or minus sign, then you will need to use custom formatting. For such a thing, you will need to select the cell where you want to get the expected result. Then open the Format Cells window (you can use the Ctrl + 1 buttons). Select Custom and in the Type field set this: +0;-0;0 and finally click the OK button.

This will cause:

• Values ​​above 600 to be displayed with + (e.g. +30).
• Values ​​below 600 to be displayed with - (e.g. -10).
• If the value is exactly 600, 0 will be displayed.

Hope this helps.

IlirU

Answer 3

to simplify the following...

If we "assume" your equation is correct, and you want to just simplify it, just note that it is not written very clearly.

That is why the "helpful" suggestions will give a different solution.

Yours is B5, other in B6.

Answer 4

Since your ranges are contiguous blocks, your first formula can also be shortened to:

=SUM(IF($B$2:$E$2=0,0,IF($B$2:$E$2>$I$2:$L$2,4,IF($B$2:$E$2=$I$2:$L$2,2))))

Your second question isn't clear to me as to what result you want.

Answer 5

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