Conditional formula evaluation

[Sushila123]

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!

[#2]

@Sushila123 

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.

[#3 · Accepted solution]

@Sushila123 

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]

Just a note to go with @Hans Vogelaar 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 

[#5]

Thank you @mathetes, this is exactly what I was looking for! Works perfectly.
Cheers!