If outcome of Formula is 0 show blank

Jeffrey Wagemans

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!

Paul Newcome

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

Jana Brits

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

Paul Newcome

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

Jana Brits

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

Jeffrey Wagemans

Thanks it worked!!

Paul Newcome

Happy to help! 👍️