Multiple Countifs and Children to use in a Dashboard

Hello Everyone!

I'm new to figuring out counts in SmartSheets and Dashboads/Sights and could use a little help.

I have a sheet that is connected to Salesforce via the Salesforce Connector, so the data is dynamic.  I currently have the sheet grouped by department.  Under each department are children rows for projects.  There is a column for project name and a column for status.  The status are Green, Yellow, Red.

I'm trying to create a dashboard for this data that will show the counts of

  1. Number of projects that are Green in each department
  2. Number of projects that are Yellow in each department
  3. Number of projects that are Red in each department
  4. Number of overall projects that are Green, Red, and Yellow

https://app.smartsheet.com/b/publish?EQBCT=1d95c68ce69b42ecaeed00fcb1f46e05

For #1, I have used =COUNTIF(CHILDREN(), "Green") and placed this is the Status Column on the Department Parent Rows.  Since the Departments don't change in Salesforce, that count works out just fine even as new projects come on board or leave.

How can I get the counts for Yellow and Red somewhere else in the sheet (or in another sheet)?  I.e., is there are a way to use the CHILDREN() in a different column than the one where the data is? I can't seem to figure that out.

Since I am creating a chart with it in Dashboard/Sights, I need these counts to be in their own column somewhere so I can put them into a chart.  I've seen solutions to count all 3 values and display them in the same column, but not in different columns/sheets.

Any advice would be most appreciated.

Thank you! 

PS. I am learning a lot from this community.

 

 

 

 


Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/10/18

    Hi,

    Here's an example formula: =COUNTIF(CHILDREN(Status1), "Green")

    It's also in your sample sheet for Green, Yellow and Red.

    I hope this helps you!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thanks so much, Andrée!

    So since my data is changing as it comes in from Salesforce, will the sums continue to work?  For example, for Department B, the formula is:  =COUNTIF(CHILDREN(Status5), "Green")

    If Department A gets a few more rows added to it, Department B would no longer be in Row 5.  So I'm wondering if that dynamic nature will works regardless of the row numbers.

    Appreciate your help - so great!

    Melissa

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Sums

    It depends on where the data from Salesforce end up. If it gets moved to the right location and is a child, it will work, but otherwise, you would need to use a different setup with a formula referencing the various departments or similar.

    Row numbers

    The formula for department B will update itself when there are new rows in department A.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Got it!  That makes perfect sense.  The Salesforce connector moves the data into the right location as a child, so it sounds like it will work.  Thanks again!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    I haven't worked with the Salesforce integration yet, so it's great to hear that the information moves to the right section and that it works for you.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.