Forum Discussion
Solved
Conditional formula evaluation
Hi, I would like to evaluate a SUMIFS formula so that if it produces a result greater than 0, it should print that value, and if it produces 0, it should instead be an empty field. What I've cur...
Assuming you have the most recent version of Excel, you could employ the LET function to avoid the redundancy you speak of. Here's a reference.https://exceljet.net/excel-functions/excel-let-function
Given the formula you've shared, =IF(SUMIFS(...)>0,SUMIFS(...),""),
LET would enable you to write something along these lines:
=LET(res,SUMIFS(...),IF(res>0,res,""))
If you keep the original =SUMIFS(...) formula, you have two options:
- Select File > Options > Advanced. Scroll down to the section "Display options for this worksheet", clear the check box "Show a zero in cells that have zero value" and click OK.
- Select the cells with the SUMIFS formula, and apply a custom number format to them, for example
#,##0.00;-#,##0.00;
The ; at the end effectively hides zero values.
Just a note to go with HansVogelaar solution, if a negative number is possible then change the formula to =MAX( SUMIFS(...), 0)
and apply either of the bullet points to make the 0 values blank
but if you have Excel 365 I recommend the LET() command mentioned by mathetes