Stacked Bar Chart using Metrics and multiple data sets

EliseB
EliseB ✭
edited 05/26/22 in Formulas and Functions
Smartsheet Metrics.PNG

Hello, I am struggling to gather data together from multiple data sets to represent as a stacked bar chart. I have large spreadsheets that use dropdown options for data I need such as customer journey and impact rating (high, medium, low) there is a lot of other data in these sheets as well and they are over the 100,000 limit so I am trying to be specific when inserting a data reference (difficult with how the sheets are set up that is out of my control).

Based on things I have read here I have created a metric sheet where I need to pull agency specific information and use COUNTIFS formulas to count the number of High, Medium and low impacts per customer journey. I am struggling to pull the data and a little frustrated that all this work is required for one tile on a dashboard that then needs to be duplicated for several agencies.

I am pretty sure I am super off with the formula and have spent too much time trying to work it out!

Can someone please help me?

Thanks,

Elise

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @EliseB

    If I'm understanding you correctly, you have multiple sheets that have specific Customer Journeys listed and then also a column that indicates the Impact Rating in text.

    If so, you're on the right track with COUNTIFS! You will need one COUNTIFS formula per sheet, then add them all together for your total number.


    For example:

    =COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High")

    Then you can repeat this for Sheet 2 and add it together:

    =COUNTIFS(sheet 1) + COUNTIFS(sheet 2) + COUNTIFS(sheet 3)


    =COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High") + COUNTIFS({Customer Journey Column Sheet 2}, [Customer Journey]@row, {Impact Rating Sheet 2}, "High") + COUNTIFS({Customer Journey Column Sheet 3}, [Customer Journey]@row, {Impact Rating Sheet 3}, "High")


    Once you've built this out for "High", you can copy this same formula into the next column and adjust "High" to be "Medium" etc. Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • EliseB
    EliseB ✭

    I've realised I probably need a kind of lookup and sum function, lookup the value in the customer journey column and then return the sum of the total high impacts in all sheets matching the high/customer journey arguments. Has anyone got any advice ?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @EliseB

    If I'm understanding you correctly, you have multiple sheets that have specific Customer Journeys listed and then also a column that indicates the Impact Rating in text.

    If so, you're on the right track with COUNTIFS! You will need one COUNTIFS formula per sheet, then add them all together for your total number.


    For example:

    =COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High")

    Then you can repeat this for Sheet 2 and add it together:

    =COUNTIFS(sheet 1) + COUNTIFS(sheet 2) + COUNTIFS(sheet 3)


    =COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High") + COUNTIFS({Customer Journey Column Sheet 2}, [Customer Journey]@row, {Impact Rating Sheet 2}, "High") + COUNTIFS({Customer Journey Column Sheet 3}, [Customer Journey]@row, {Impact Rating Sheet 3}, "High")


    Once you've built this out for "High", you can copy this same formula into the next column and adjust "High" to be "Medium" etc. Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • EliseB
    EliseB ✭

    Thanks Genevieve,

    Due to the volume of reports and dashboards and the tight timeline we had I decided to go a different way and utilised pie charts and columns to split the data using the pre- existing reports with the view to refine this down the track when there is more time!

    Thanks for these details I will save it and reference back when we want to revisit the option

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!