How to return blank rather than 0 with COUNTIF formula

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.
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!