This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(304, 34%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELC%3C/text%3E%3C/svg%3E)
=(ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600)
Using the formula above how can I show a blank or a 0 value in column N2, instead of -600 until I enter data in columns H:L
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Normally you would write something like IF(my_expression<0,0,my_expression) but having to repeat the expression is both inefficient and error prone. The LET function address this concern.
=LET(x,ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0),IF(x<0,0,x)
I don't know what the +0 at the end of ABS accomplishes.
When using my formula in column N to capture the value different in E & L, plus or minus +/- a value of 600, when data have been entered on my spreadsheet. However; my goal is not having a -600 value shown, but to show a blank value in column N, when there is no data in columns A:E,H:L......+0)-600) in my formula is the only way to avoid the Microsoft formula error message. =IF(COUNTA($E2:L2)=0,0,ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600)
Thanks
LeRoy
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(169.60000000000002, 94%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDD%3C/text%3E%3C/svg%3E)
until I enter data in columns H:L
[edited]
Hi,
Try this
=if(count($E2:$L2)=0,"",(ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600))
Here is a complete format of my worksheet and I am trying to calculate the total with a +- formula to display empty cells in N2 until I enter data in cells B2:D2 and or I2:K2 which subtotal into the TOTAL columns with my formula
=(ABS(SUMIFS($E2:$L2,$A2:$H2,"COFIELD")+0)-600)