How to return blank rather than 0 with COUNTIF formula

Jan Peters

Hi -

I have a 'COUNTIF' formula in a Roll-up Summary that is referencing a column with dates in another sheet to provide a count of dates within a specific range. If no dates are found within the date range specified, it is returning a '0'. Is there a way to have it NOT return the 0 but return a blank instead?

Here's my formula:

=COUNTIFS({Rapid Response Opportunity Tracking Range 9}, >=DATE(2020, 3, 30), {Rapid Response Opportunity Tracking Range 9}, <=DATE(2020, 4, 3))

This detail then feeds into a chart in a dashboard which looks pretty awful with a bunch of zeros.

I've tried everything that I can find online and so far, no luck, Hoping that you can help me out!

Thanks,

Jan

PS - I've also tried conditional formatting to make the zeros white but that doesn't carry through into the chart in the dashboard for some reason.

steven.reed14881

Jan,

There are a couple approaches you can take here:

-- You can adjust your formula to return a blank by updating it by adding a simple if statement at the start, as follows: =if({Rapid Response Opportunity Tracking Range 9} = 0, "", COUNTIFS({Rapid Response Opportunity Tracking Range 9}, >=DATE(2020, 3, 30), {Rapid Response Opportunity Tracking Range 9}, <=DATE(2020, 4, 3)). [this may need slight tweaks]

-- Another option might be cleaner though. You can create a report from your data sheet that only pulls weeks with non-0 values. Then you can point your chart to that report, and it will exclude any week that has a 0 result.

Hopefully this will be helpful to you.

Steve