COUNTIFS based on Children criteria of certain row

Sandy Glassberg
Sandy Glassberg ✭✭✭
edited 01/16/20 in Formulas and Functions

Context: Please see screenshot attached. Project Manager "Fred" has 2 "active" projects in January: Project 1 and Project 2, which are Children under "Fred". This count of 2 is determined by some percentage of the project being completed.


Goal: Count the number of active projects per month based on % complete being greater than 0.


Request: Can someone please provide me with the appropriate COUNTIF formula based on these criteria? Thank you so much in advance!


Answers

  • A colleague helped me figure it out! Here's the formula he came up with:

    (using a hidden helper column called "PM")

    =COUNTIFS(CHILDREN($PM$12), "Fred", CHILDREN([Jan 2020]12).

    I was having issues at first because various rows were not indented properly and created circular references. But this appears to work fine! If anyone has better suggestions, I'm all ears.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Sandy,

    Excellent!

    Thanks for sharing!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Please help the Community by marking the post that helped answer your question or solve your problem with the accepted answer. It will make it easier for others to find a solution or help to answer!

    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 for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sandy Glassberg

    I believe part of your formula may be missing. You have the range but not the criteria for your second set. Could you please post the full formula?


    Additionally... Please don't forget to mark the most appropriate response as being helpful. This will mark your post as having an "Accepted Answer" and will let others having a similar issue know that a solution can be found.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!