# Formula to remove zero on metric sheet

Hello - I am using a metric sheet to pull together a Dashboard of information. I am pulling from a larger sheet into the metric sheet and then importing the data into a Dashboard either as a chart or metric.

The data I am pulling changes every month and I want to simply total what is being processed each month. Some months there are zero of a particular item and I don't want to record those in the Dashboard. I tried filter in the metric sheet, but that doesn't translate to the Dashboard.

This is the formula I am using =COUNTIFS({Status2}, Status@row, {Deliverables}, [Primary Column]@row)

Any guidance would be so appreciated!!

Tags:

• ✭✭✭✭✭✭

I like to use an IF statement to say that if the calculation does not equal zero then run it.

=IF(COUNTIFS({Status2}, Status@row, {Deliverables}, [Primary Column]@row) <> 0, COUNTIFS({Status2}, Status@row, {Deliverables}, [Primary Column]@row))

• ✭✭✭✭✭✭

I like to use an IF statement to say that if the calculation does not equal zero then run it.

=IF(COUNTIFS({Status2}, Status@row, {Deliverables}, [Primary Column]@row) <> 0, COUNTIFS({Status2}, Status@row, {Deliverables}, [Primary Column]@row))

• Paul - this worked really great. Thank you so much! I really appreciate your assistance!

• ✭✭✭✭✭✭

Happy to help. 👍️

• edited 05/24/23

Hi, My formula isn't working, could someone please let me know what I am doing wrong? I am getting unparseable error.

This is my original formula which is giving a number but the moment I apply If its returning to unparseable. =(COUNTIFS({IAT Data Analysis Range 1}, \$Metrics\$245, {IAT Data Analysis Range 5}, \$Metrics\$246, {IAT Data Analysis Range 3}, \$Metrics@row, {IAT Data Analysis Range 2}, wsq\$245)) / wsq\$27

I am applying If here: if(COUNTIFS({IAT Data Analysis Range 1}, \$Metrics\$245, {IAT Data Analysis Range 5}, \$Metrics\$246, {IAT Data Analysis Range 3}, \$Metrics@row, {IAT Data Analysis Range 2}, wsq\$245)) / wsq\$27>0, (COUNTIFS({IAT Data Analysis Range 1}, \$Metrics\$245, {IAT Data Analysis Range 5}, \$Metrics\$246, {IAT Data Analysis Range 3}, \$Metrics@row, {IAT Data Analysis Range 2}, wsq\$245)) / wsq\$27

• ✭✭✭✭✭✭

@Sarita It looks like you have some parenthesis issues. Try this:

=IF(COUNTIFS({IAT Data Analysis Range 1}, \$Metrics\$245, {IAT Data Analysis Range 5}, \$Metrics\$246, {IAT Data Analysis Range 3}, \$Metrics@row, {IAT Data Analysis Range 2}, wsq\$245) / wsq\$27>0, (COUNTIFS({IAT Data Analysis Range 1}, \$Metrics\$245, {IAT Data Analysis Range 5}, \$Metrics\$246, {IAT Data Analysis Range 3}, \$Metrics@row, {IAT Data Analysis Range 2}, wsq\$245) / wsq\$27)

• Still same error. The same if thing works in other formula wherein there no division at the end. Do you think division at the end of the formula is a problem?

• ✭✭✭✭✭✭

@Sarita Sah The division should not have an impact. Have you ensured that the column name "wsq" does in fact exist on your sheet?

• ✭✭✭✭✭

@Paul Newcome just reading this old post and it's sort of what I need. But I can't see how it fits in my formula. I am plotting a line chart and I do want the future months to show zero on the chart so I want to keep them blank until there is a count.

=COUNTIFS({Created}, IFERROR(MONTH(@cell), 0) = March1, {Created}, IFERROR(YEAR(@cell), 0) = \$[Tickets Year]@row)

• ✭✭✭✭✭✭

@mccoy_FSI You would use the same logic. If the COUNTIFS does not equal zero, then output the COUNTIFS.

=IF(COUNTIFS(.....) <> 0, COUNTIFS(.....))

• ✭✭✭✭✭

@Paul Newcome Like this? I still get 0 and then cells that have a count are unparseable.

=IF(COUNTIFS({Created}, IFERROR(MONTH(@cell), 0) = January1, {Created}, IFERROR(YEAR(@cell), 0) = \$[Tickets Year]@row), <>0, COUNTIFS({Created}, IFERROR(MONTH(@cell), 0) = January1, {Created}, IFERROR(YEAR(@cell), 0) = \$[Tickets Year]@row))

• ✭✭✭✭✭✭

@mccoy_FSI Almost. You need to remove the comma before <> 0.

• ✭✭✭✭✭

@Paul Newcome Yes it works! You are the man.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!