Countif children row with reference another sheet

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

I want to count the children rows in another sheet that contain a certain value, my attempt gets an #UNPARSEABLE

 

=COUNTIF(CHILDREN(){Lease Tracker 2018 Manager},="CORNELIUS"))

 

Any thoughts? THANKS!

Tags:
· Share on FacebookShare on Twitter

Comments

  • Sterling
    Sterling ✭✭
    edited 11/01/18

    Unfortunately the children function can't be used for referencing another sheet. That said to get the children function to work properly you would need to put the cell you want to count the children of inside the parenthesis.

    CHILDREN([CELL])

    This link has some helpful information for referencing other sheets. I hope you can find something that works for you!

    · Share on FacebookShare on Twitter
  • KO_in_Colo
    KO_in_Colo ✭

    Paul,

    Yes, thank-you! This is exactly my conclusion after sitting here working on this project for some time. I can create some new (hidden) columns since the info is just for me to use for Dashboard metrics.  I want to be in a different column and count the children rows that have Cornelius in the manager column. And I will need to do for all of the other managers.  I've been sitting here trying to figure out how to create the formula but I haven't had any success. I was trying COUNTIF(CHILDREN(), ="Cornelius") ... but not sure how to reference the Manager Column. Any thoughts?

    · Share on FacebookShare on Twitter
  • KO_in_Colo
    KO_in_Colo ✭

    Thanks for responding!

    · Share on FacebookShare on Twitter
  • eric.o
    eric.o Employee
    edited 11/01/18

    Happy to help!

     Currently, we don’t have a method to utilize the CHILDREN function in a Cross-Sheet Formula but this will be considered as a possibility for future development. 

    You may be able to achieve your desired goal a couple of ways.

    1. You can, instead of utilizing a CHILDREN Function, reference the desired range of cells on the other sheet. You can achieve this by selecting on the column header of the desired sheet, or by selecting the first cell and the last cell of the desired range.

    2. Create the COUNTIF on the Origin sheet then on the Destination sheet reference the cell containing this formula, utilizing Cell Linking: https://help.smartsheet.com/articles/861579-cell-linking

    The Origin sheet Formula could look like this:

    =COUNTIF(CHILDREN([Desired Parent Rows Column Name Delete If In Parent Row]13), "CORNELIUS")

     

    Cheers, 

    Eric  

    Smartsheet Support

    · 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!