Metric sheet with a SUMIF formula to create a stacked bar

Options

Answers

  • Brittney Sandoval
    Options

    Are there screenshots or a document with more detailed instructions for this formula?

  • Genevieve P.
    Options

    Hey @Brittney Sandoval

    Yes! 🙂

    Can you define your specific scenario? I can base some screen captures off of that for you.

    Otherwise here are some resources for you:

    The webinar is step-by-step instruction that I would suggest.

    Cheers,
    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Brittney Sandoval
    Options

    @Genevieve P. Thank you for the reply.

    I have an issue log in which I am trying to cross reference into a Metric sheet. Root Cause column is a single select drop down. The other column is a Name single select drop down.

    I would like to pull this information into my Metric sheet with a SUMIF formula so I am able to create a stacked bar with each bar being a Root Cause, and each section of the bar references the number of issues that each Name entered by the Root Cause.

    Example: There are a total of 20 Support issues. John entered 3 Support issues, Jane entered 9 Support issues, etc.

    I have a list of 8 Root Causes with a list of 100+ Names. I am also open to flipping the data and each bar would reference a name and each section reference a root cause.

  • Genevieve P.
    Options

    Hi @Brittney Sandoval

    Thank you for explaining your scenario more! The SUMIF formula requires numbers to sum together - in your instance you'd need a COUNTIFS formula instead, to count the number of times a specific Root Cause is associated with a person.

    I actually think in your scenario it would be easiest to skip the formulas, since you have so many Names. This is what I would do:

    • Create 8 Root Cause Row Reports, one for each Root
    • Group by the Name column
    • Use Summary to COUNT by the Name column
    • Create 8 bar charts, using a row report for each chart

    Let me know if this makes sense and will work for you!

    Cheers,
    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Brittney Sandoval
    Options

    @Genevieve P. I asked for a stacked bar. I already have 8 separate graphs and need it to be consolidated into 1 graph that is a stacked bar, which is why I made the request. I provided 2 acceptable options.

    Please provide instructions on a STACKED BAR chart in your next reply.

  • Genevieve P.
    Options

    Hi @Brittney Sandoval

    The steps above work for a Stacked Bar chart. 🙂 You would need to create 1 chart per Report, but this will be the easiest way to generate your visuals without needing formulas or needing to identify each one of your 200+ users.

    Here are the steps with visuals:

    Source Sheet Example

    Report - filtering by 1 of your Root Causes:

    Report - Grouped by Name (so you can add more people and never need to update a formula… the Report will automatically add new names as they come in)

    Report - COUNT Summary by Name

    Grouped Report used as a source for a Stacked Bar chart (these are the settings):

    So then you could simply create a copy of the Report with "Save as New" and change the filter to look for your second root cause, and have that as a bar chart immediately below the current one.

    Let me know if this makes sense or if I can clarify anything further!

    Cheers,
    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Brittney Sandoval
    Options

    @Genevieve P. This is exactly how I had it set up. The issue not that your instructions are unclear, the issue is that I now need 8 stacked bar charts to display in the dashboard. I need all of the data to be in One (1) single chart, which was explained above. Please let me know if this is possible with Smartsheet, or if is it a function not supported.

  • Genevieve P.
    Options

    Hi @Brittney Sandoval

    A grouped Report with multiple, individual charts would be the easiest to maintain, but if you need all the bars to be within the same graph, then you could use formulas in a metric sheet to format your data. This would require you to add each Name down one column, then use each column title as the root cause.

    For example:

    Then you would use a cross-sheet COUNTIFS formula to count how many times the name in the current row is associated with the cause of the current column.

    Once your data is in a sheet format like this, you can set up your chart in either of your preferred configurations:

    or switch the rows & columns:

    Here are some resources for you:

    Again, I would highly recommend the webinar as a good place to start.

    Cheers,
    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!