Metric sheet with a SUMIF formula to create a stacked bar

System

This discussion was created from comments split from: Create Stacked Bar Chart from multiple columns of a summary report.

Brittney Sandoval

https://community.smartsheet.com/discussion/comment/308590#Comment_308590

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

Genevieve P.

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:

• SUMIF Function
• Steps to create a Cross Sheet Reference
• Webinar that includes creating a cross-sheet SUMIF formula (Part 2, 25min in)
  

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

Cheers,
Genevieve

Brittney Sandoval

@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.

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

Brittney Sandoval

@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.

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

Brittney Sandoval

@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.

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:

• COUNTIFS Function
• Steps to create a Cross Sheet Reference
• Webinar that includes creating a cross-sheet formula

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

Cheers,
Genevieve