Forum Discussion
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 currently got is =IF(SUMIFS(...)>0,SUMIFS(...),"") where SUMIFS(...) is short for the full formula.
It works exactly how I want it to, but it seems inefficient to write it twice in an IF formula like that, and then if I ever want to change the criteria I have to do it twice in the same place.
Any suggestions please?
Cheers!
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,""))
4 Replies
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,""))
- Thank you mathetes, this is exactly what I was looking for! Works perfectly.
Cheers!
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 blankbut if you have Excel 365 I recommend the LET() command mentioned by mathetes
