Formula to remove zero on metric sheet

Noelle Nordstrom

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)

Snapshot.jpg

Any guidance would be so appreciated!!

Paul Newcome

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 Newcome

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

Noelle Nordstrom

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

Paul Newcome

Happy to help. 👍️

Sarita Sah

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

Paul Newcome

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

Sarita Sah

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?

Paul Newcome

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

mccoy_FSI

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

Paul Newcome

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

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

mccoy_FSI

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

Paul Newcome

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

mccoy_FSI

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