How do I hide the resulting zero from SUM IF formulas?

sadams2010
sadams2010 ✭✭
edited 05/31/23 in Formulas and Functions

I am currently working with the following SUM IF formula, and I need to hide the results of 0 to declutter the sheet. This is my current formula:

=SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1})

I tried this variation: =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}), = 0, "") but it returned a blank even though the sum is 5.

I've tried multiple variations of the formula to no avail. Any assistance would be greatly appreciated.

Best Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 05/31/23 Answer ✓

    Hi @sadams2010,

    I would use an IF statement.

    IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1})) > 0, (SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1})), “”)

    This says if your formula result is greater than 0, then execute your formula and use the result, "else" use the blank string which is double quotation marks "".

    Best of luck!

    BRgds,

    -Ray

  • Macorne
    Macorne ✭✭✭✭
    Answer ✓

    Close! You should be able to do a quick edit to the formula to get there. Remember that the If formula works like this:

    =if(conditional is true, then return this, else return this)


    =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}), = 0, "")

    In your original formula above, the edit needs to happen to the =0 line. you need to take out the comma before the "=0"


    Should be:

    =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}) = 0, "", SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}))

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 05/31/23 Answer ✓

    Hi @sadams2010,

    I would use an IF statement.

    IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1})) > 0, (SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1})), “”)

    This says if your formula result is greater than 0, then execute your formula and use the result, "else" use the blank string which is double quotation marks "".

    Best of luck!

    BRgds,

    -Ray

  • Macorne
    Macorne ✭✭✭✭
    Answer ✓

    Close! You should be able to do a quick edit to the formula to get there. Remember that the If formula works like this:

    =if(conditional is true, then return this, else return this)


    =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}), = 0, "")

    In your original formula above, the edit needs to happen to the =0 line. you need to take out the comma before the "=0"


    Should be:

    =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}) = 0, "", SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}))

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    @Macorne,

    I think we did the same thing in reverse.

    You said if equals 0, do this.

    I said if greater than 0, do this.

    I did edit my original post because typing the greater than sign in these text boxes causes it to delete itself. I had to add the greater than 0 back in because it had been deleted.

  • Thank you @Ray Lindstrom and @Macorne for the explanations and examples! The formula edits that you provided worked perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!