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))))
-
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))))
-
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!!
-
Help Article Resources
Categories
Check out the Formula Handbook template!