Multiple Countifs and Children to use in a Dashboard

mcheung57551

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.

 

 

 

 

Andrée Starå

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

mcheung57551

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å

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

mcheung57551

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å

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.