Please help resolving my cell value issue

It looks like the issue is that your current test is COUNTA($E2:L2)=0, which only checks columns E through L. You want column N to remain blank until there is data somewhere in A:K.

Use:

=IF(COUNTA($A2:$K2)=0,"",ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600)

This does three things:

• COUNTA($A2:$K2)=0 → if A through K are completely empty, N is blank.
• SUMIFS(...) → finds the COFIELD row/player and obtains the applicable total.
• +0)-600 → forces the result to numeric and allows the negative difference to display correctly. For example, 598 - 600 = -2.

So with your example, TOTAL = 598, and the result in column N is -2 instead of -600 when the row has no data.

Note that if cells A:K contain formulas that return "", COUNTA can still regard those cells as populated. If that is the situation in your spreadsheet, use this version instead:

=IF(SUMPRODUCT(--($A2:$K2<>""))=0,"",ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600)

That version checks whether A:K actually contain displayed values, rather than simply whether the cells technically contain something.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin