If outcome of Formula is 0 show blank
Hi All,
I'm using a formula to count how many stores we have opened in a certain country/month. This information is then used in a chart in a dashboard. The problem is that when we haven't opened any store it shows a zero. This zero is also displayed in the chart, and don't want this. So is it possible to make the formula work in way that when the outcome is zero show blank?
I'm using the below formula to count every store we have opened.
=COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31)))
Thanks all!
Best Answers
-
You can accomplish this using an IF statement (I do this rather frequently).
=IF(original formula > 0, original formula)
=IF(COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))) > 0, COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hey Jeffrey
Try this formula:
=IF(COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))) = 0; ""; COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))))
Kind regards,
Jana
Answers
-
You can accomplish this using an IF statement (I do this rather frequently).
=IF(original formula > 0, original formula)
=IF(COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))) > 0, COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hey Jeffrey
Try this formula:
=IF(COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))) = 0; ""; COUNTIFS({Master Summary Roll Up - Boss Sheet Range 2}; "Netherlands"; {Master Summary Roll Up - Boss Sheet Range 3}; "New Location"; {Master Summary Roll Up - Boss Sheet Range 1}; 1; {Master Summary Roll Up - Boss Sheet Range 4}; AND(@cell >= DATE(2019; 7; 1); @cell <= DATE(2019; 7; 31))))
Kind regards,
Jana
-
Thanks it worked!!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!