Multiple Countifs and Children to use in a Dashboard

mcheung57551 ✭✭
edited 12/09/19 in Formulas and Functions

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

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.






Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!