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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @Marilen.Navarro103391<\/a> <\/p>

To count blanks in a formula, you can count how many cells have: \"//www.santa-greenland.com/community/discussion/30446/\"<\/p>

=COUNTIF([Column]:[Column], \"//www.santa-greenland.com/community/discussion/30446/\")<\/p>

So in your case, instead of referencing a blank cell, try:<\/p>

=COUNTIFS({Battery CE Leads Range 3}, \"Unqualified\", {Battery CE Leads Range 4}, \"//www.santa-greenland.com/community/discussion/30446/\", <\/strong>{Battery CE Leads Range 2}, AND(IFERROR(MONTH(@cell), 0) = Apr$1, IFERROR(YEAR(@cell), 0) = 2023))<\/p>


<\/p>

You will also need to add the month number, <\/strong>4, in the cell Apr$1<\/strong>. This will get the formula to look for the month of 4, or April. If you leave that blank then your result will likely be 0. <\/p>

Let us know if that helped!<\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107120,"type":"question","name":"Start date auto update?","excerpt":"Hi, everyone. I'm very new to using Smartsheet and am having some troubles regarding start\/finish dates. I'm trying to find and use the correct formula\/automation to change the start dates if the previous job task has run over its allocated time. For example, I use Smartsheet to schedule trades for job sites, if a tiler…","snippet":"Hi, everyone. I'm very new to using Smartsheet and am having some troubles regarding start\/finish dates. I'm trying to find and use the correct formula\/automation to change the…","categoryID":322,"dateInserted":"2023-06-30T02:01:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-02T23:48:42+00:00","insertUserID":163033,"insertUser":{"userID":163033,"name":"Ashleigh","url":"https:\/\/community.smartsheet.com\/profile\/Ashleigh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T03:00:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163033,"lastUser":{"userID":163033,"name":"Ashleigh","url":"https:\/\/community.smartsheet.com\/profile\/Ashleigh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T03:00:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3376433395,"url":"https:\/\/community.smartsheet.com\/discussion\/107120\/start-date-auto-update","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107120\/start-date-auto-update","format":"Rich","tagIDs":[254,281,334],"lastPost":{"discussionID":107120,"commentID":383457,"name":"Re: Start date auto update?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383457#Comment_383457","dateInserted":"2023-07-02T23:48:42+00:00","insertUserID":163033,"insertUser":{"userID":163033,"name":"Ashleigh","url":"https:\/\/community.smartsheet.com\/profile\/Ashleigh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T03:00:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-02T23:47:59+00:00","dateAnswered":"2023-06-30T04:13:25+00:00","acceptedAnswers":[{"commentID":383257,"body":"

Hi @Ashleigh<\/a> <\/p>

You could use the predecessor column: set the Type to \"Finish-to-Start\" and make the Lag 0 for the next day and so on, for the row you want. You'll need to enable dependencies on the sheet. You can check that by going to the Gantt view and then clicking the settings wheel under the share button.<\/p>

I hope that helps.<\/p>

Matt<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":281,"urlcode":"Support","name":"Support"},{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":107146,"type":"question","name":"How to get average # Workdays by Contract Type","excerpt":"I'm stumped and hoping the community can help. Within Sheet Summary of my master grid, I am trying to create an average formula that calculates the average # of workdays by contract type within a specific month--in this case, June 2023. This data will be used in a Monthly Dashboard. For example: \"NDA\/CDA (Non-Disclosure…","snippet":"I'm stumped and hoping the community can help. Within Sheet Summary of my master grid, I am trying to create an average formula that calculates the average # of workdays by…","categoryID":322,"dateInserted":"2023-06-30T15:56:12+00:00","dateUpdated":"2023-06-30T16:35:53+00:00","dateLastComment":"2023-06-30T18:56:28+00:00","insertUserID":159351,"insertUser":{"userID":159351,"name":"Joan156","url":"https:\/\/community.smartsheet.com\/profile\/Joan156","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T18:57:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-03T04:24:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":54,"score":null,"hot":3376296760,"url":"https:\/\/community.smartsheet.com\/discussion\/107146\/how-to-get-average-workdays-by-contract-type","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107146\/how-to-get-average-workdays-by-contract-type","format":"Rich","lastPost":{"discussionID":107146,"commentID":383393,"name":"Re: How to get average # Workdays by Contract Type","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383393#Comment_383393","dateInserted":"2023-06-30T18:56:28+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-03T04:24:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/NS3PAA2S9KQH\/screenshot-for-smartsheet-community.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot for SmartSheet Community.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T08:51:54+00:00","dateAnswered":"2023-06-30T17:29:26+00:00","acceptedAnswers":[{"commentID":383368,"body":"

=AVG(COLLECT([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], [Contract Type]:[Contract Type], \"NDA\/CDA (Non-Disclosure Agreement)\", [Today's Date]:[Today's Date], MONTH(@cell) = 6, [Today's Date]:[Today's Date], YEAR(@cell) = 2023))<\/p>

You will just need to substitute in Month\/Year\/Contract Type as needed.<\/p>"},{"commentID":383393,"body":"

Awesome! I'm glad it's working for you. 👍️<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions