SUM Cells with COUNTIF Formula returning zero value

Sunil Kadikar

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?

Danielle O'Connell

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).

Danielle O'Connell

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

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

@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!