Removing Zero Values From Chart

kelceyg

I am trying to create a stacked column chart to show how many Statuses are being used per section (Section for Chart) in a project plan. However, I do not want any zero values to show. I have attempted to create a report for this chart, but I cannot get the chart to look the way that I want.

What I want (minus the zero values):

This is how the source data is structured:

If I use a report, I just get one solid bar for the overall status count per section. I would like it stacked with a count of each individual status per section. I have tried using a helper sheet to pull together the status counts in each section, and then tried to use the helper to create the chart directly or to create a report, but neither produced the results that I am looking for.

Any help, thoughts, or suggestions would be greatly appreciated!

Sarah Keortge

Charts can be a little tricky in Smartsheet. My recommendation would be to create a separate summary sheet to calculate the status count by section in a table format.

Here's my mockup of your source sheet:

And then here's a separate summary sheet where I've calculated the status by section in a format that works well for stacked column charts.

The cross-sheet formula for each status column is something like

=COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client")

And here's the resulting dashboard chart using the summary sheet.

The chart's source data is inclusive of that 4th status but it's only going to display the horizontal axis for the sections I have listed in the summary sheet.

If you would prefer to exclude any status that don't appear in your schedule, then in your summary sheet, replace zeroes with a blank by modifying the formula to first check if the count will return a zero and if it does then swap in a "" blank and otherwise return the count.

=IF(COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client")=0,"",COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client"))

which will look like this on your summary sheet

And look like this on your chart

Now, if your sections are standardized, you can simply list them out on your summary sheet. However, if your sections might change, you can make your summary sheet dynamic by using an INDEX/COLLECT function. If you find a need for that, let me know and I would be happy to elaborate on how to accomplish it.

Sarah Keortge

Charts can be a little tricky in Smartsheet. My recommendation would be to create a separate summary sheet to calculate the status count by section in a table format.

Here's my mockup of your source sheet:

And then here's a separate summary sheet where I've calculated the status by section in a format that works well for stacked column charts.

The cross-sheet formula for each status column is something like

=COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client")

And here's the resulting dashboard chart using the summary sheet.

The chart's source data is inclusive of that 4th status but it's only going to display the horizontal axis for the sections I have listed in the summary sheet.

If you would prefer to exclude any status that don't appear in your schedule, then in your summary sheet, replace zeroes with a blank by modifying the formula to first check if the count will return a zero and if it does then swap in a "" blank and otherwise return the count.

=IF(COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client")=0,"",COUNTIFS({Section for Chart column from schedule}, [Section for Chart]@row, {Status column from schedule}, "1 - Selections/Template Sent to Client"))

which will look like this on your summary sheet

And look like this on your chart

Now, if your sections are standardized, you can simply list them out on your summary sheet. However, if your sections might change, you can make your summary sheet dynamic by using an INDEX/COLLECT function. If you find a need for that, let me know and I would be happy to elaborate on how to accomplish it.

kelceyg

Thank you, @Sarah Keortge! The IF(COUNIF( formula got me where I needed to be!