Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Countif(Children(), Criteria)

03/30/16 Edited 12/09/19

Hi 

I use this  formula to count how many Red (RYG) there are for a parent task

Countif(Children(), "Red")

I find that I have to place this in the parent line and in the RYG column

 

What do i do if I want to count the "Yellow"s as well?

By Extension, in Status we have more than 2 statuses so how do you do a countif for the various possibilities ?

 

Thank You

 

Comments

  • Hi Irene,

     

    Here's is a forumula I believe is useful to this situation. The folmula counts different colors and put together as a descriptive text:

     

    ="Red (" + COUNTIF(CHILDREN(), "Red") + "), Yellow (" + COUNTIF(CHILDREN(), "Yellow") + "), Green (" + COUNTIF(CHILDREN(), "Green") + ")"

     

    Capture.JPG

  • Good Idea!!

     

    Thank You Very much!

  • J. Craig WilliamsJ. Craig Williams Top Contributor
    edited 03/31/16

    Irene,

     

    In regards to your "I find that I have to place this in the parent line and in the RYG column" comment

     

    You can also move the formula somewhere else.

    The CHILDREN() formula will take an argument.

    Just point it to the place you would put it now

     

    Countif(Children([RYG]23), "Red")

     

    for the RYG column, row 23.

     

    Craig

  • Hi Craig

     

    Thank you fro replying.

    Won't the addition of the argument makes the sheet less dynamic?

    Ie. I wouldn't know if someone else inserts a series of lines with another parent/child group ?

     

    Also is there a way to dynamically count the number of parents?

     

    Regards

    Irene

     

    Regards

    Irene

     

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Irene,

     

    If I understand your comment correctly, the addition of the argument does not impact the issue you raise.

    I was just moving the location of the formula (one column over, same row, for example).

     

    An issue with parents is that they can also be grandparents.

    I typically have a checkbox column named ParentRow.

    In that column, this formula

    =IF(COUNT(CHILDREN()) > 0, 1, 0)

    will be checked if there are children.

    A formula somewhere else (because checkboxes can't take numbers greater than 1)

    =COUNTIF(ParentRow:ParentRow, 1)

    will tell you how many rows have children.

     

    Check out this thread

    https://community.smartsheet.com/discussion/include-parent-rows-reports

    for a lot of commentary on parents, children, and grandparent.

    At the end of page 2 (as I write this), there is a link from Kris Walsh to a very cool sheet to help clarify things.

     

    Craig

     

     

     

  • Hello, how do I make formula SUMIF() work only on CHILDREN ()? Maybe I should use SUMIFS() for that but I dont know how. =SUMIF($Signed2:$Contact31, "Ania", $[Contract Signed]2:$[Contract Contact]31). I would like to have CHILDREN() of those ranges only.

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Do you mean that rows 2 through 31 are the children?

    or that they might be parents?

    Why do you have two (or more columns) in your range?

    Why are you using absolute references for your columns?

    Craig

     

     

  • Hi, absolute references are not needed. Yes, I mean that rows 2 to 31 are the children but sometimes it may be 2-29 or 2-28 thats why I would like to use CHILDREN instead. My formula is in Parent row.



    I have 2 columns in range and 2 columns because I have same dropdown list elements in both columns and it works properly.

  • J. Craig WilliamsJ. Craig Williams Top Contributor
    edited 09/06/18

    to replace your ranges with children for a single column

    =SUMIFS(CHILDREN(parent-cell-reference-for-the-sum), CHILDREN(parent-cell-reference-for-the-criterion),"Ania")

    I always use SUMIFS, as it allows more than one set of criteria and doesn't need to be changed when going from one to more than one.

    I'm wary of using this with multiple columns based on the names of your columns -- [Contract Contact] does not seem like a candidate for summing.

    If there are multiple columns involved, I would suggest splitting them into single columns like this:

    =SUMIFS(first set) + SUMIFS(second set)

    Can I you post a screen shot of your headers?

    Craig

  • Thank you, it works now. This problem is solved.

    Another quesion:Can I reference to in my formula to a column name?

    I have to make 6 of those formulas and it would be easier. 

     

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    A column reference would be

    [Column Name]:[Column Name]

    Be aware of circular references. The formula can't reside in the column it references.

    Craig

This discussion has been closed.