SUM Cells with COUNTIF Formula returning zero value

Options

I've built a summary table to count the number of "Issues" that meet certain criteria. I'm using a the COUNTIFS formula to do this.

This summary sheet feeds a dashboard that I use pie chart on to visually show the breakdown of Issue categories. Piecharts show an error in Smartsheet when they have a zero value to display, so I am using a workaround that someone else posted to have a category that has a count of 1, when everything else Sums to zero, I'm using a simple =(IF(SUMA1:A2)=0,1,0) formula for this.

The problem is that it seems to pull a SUM of 0, even with the COUNTIFS formulas are calculating a value that is greater than zero

Any advice?



Tags:

Best Answer

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Answer ✓
    Options

    This may not work for you depending on all the other moving parts of your sheet. I tried it your way and it definitely did not like the = 0 part, but would work fine if you did a value other than 0 or if you used greater than versus equals. So this is a bit backwards, but it gives you the end result:

    =IF(SUM([Column1]1:[Column1]4) > 0, SUM([Column1]1:[Column1]4), 1)

    If you put this in the sum field, it would basically generate either the sum of the 4 rows above if the value was greater than 0. If it is not, then it gives you your placeholder value of 1. You can change the "true" value to whatever works for you (IE the value isn't 0 what do you want to show up).

Answers

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Answer ✓
    Options

    This may not work for you depending on all the other moving parts of your sheet. I tried it your way and it definitely did not like the = 0 part, but would work fine if you did a value other than 0 or if you used greater than versus equals. So this is a bit backwards, but it gives you the end result:

    =IF(SUM([Column1]1:[Column1]4) > 0, SUM([Column1]1:[Column1]4), 1)

    If you put this in the sum field, it would basically generate either the sum of the 4 rows above if the value was greater than 0. If it is not, then it gives you your placeholder value of 1. You can change the "true" value to whatever works for you (IE the value isn't 0 what do you want to show up).

  • Sunil Kadikar
    Options

    Thanks Danielle, that worked! I actually went back and tried it with an "=0" as well, and now it seems to be working, wonder if it was a bug that SmartSheet fixed! Thanks for your help!

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Options

    @Sunil Kadikar that's so strange that it wasn't working for the =0 and now is. I agree must have been a bug. And really there was no reason for it not to work!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!