Counting Children Tasks with specific criteria

Options

Example:

In a large sheet - I need to look for specific "tasks" that fall under a parent row.

Parent Row has Brand, Month, Quarter, etc.

Child Rows have specific tasks.

So I need to count the number of times im asking for "Task X" if Parent Row has "Brand Y" and is released in "Q4"


Another way to explain this -

If the parent row meets specific criteria - I need to count specific sub (child) tasks...


Any ideas?

Answers

  • Louis Raoul
    Louis Raoul ✭✭✭✭
    Options

    Hi,

    From what I understand, it is a typical use of SUMIFS.

  • Genevieve P.
    Options

    Hi Brian,

    You would need to have a helper column in the sheet that brings the parent name down into the child rows so that this criteria is easily identified from within your formula. You can hide this column after the formula is created.

    Try something like this:

    =PARENT([Brand Column]@row)

    This should copy the Parent name into the child rows in this helper column. Then in your COUNTIFS formula you can use this helper column as a range, and the criteria to be a specific criteria.

    If you want an example of how this may work it would be helpful to see a screen capture of your sheet (but block out any sensitive data), with a list of the criteria you are counting, and if you are doing it cross-sheet or in a Sheet Summary field.

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Hey Genevieve - I dont want to populate the brand name in the rows below - because that would make it very hard to run other =CountIfs based on the brand. Unless im missing something. I only want to count the brand once at the parent row - but if I have Task (X) as a task under Brand (A) I want to count that.

    This is counted on a cross referenced metric sheet.


    Brand (A)

    Task (X)

    Task (y)

    Task (z)

    Brand (A)

    Task (X)

    Task (g)

    Task (y)

    Brand (B)

    Task (1)

    Task (X)

    Task (y)


    =Countifs (Brand A) and Task (x) = 2 (This would NOT count task (x) from Brand (B)


    Does this help?

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi Brian,

    Are your Tasks and Brand in the same column or are they two different columns?

    That would change the way to do this quite a bit.

    Also, does your Tasks have subtasks, if yes how do you want it to be counted?

    At best a Screenshot or two wih dummy data would help understand how your sheet is structured it.

    Also, what @Genevieve P is saying, is about adding another column to return the PARENT's name in this cell. That column could be hidden and would definately be the easiest way to do it. (In this case, it's a simple COUNTIFS)

  • Tasks and Brands are in different columns - but im counting the brands column already - so thats why i didn't want to populate the brand all the way down, i only wanted to populate in the parent row - so i get an accurate count of projects by brand - but i need to count how many times im doing task X per brand.

    there might be 7 tasks under a brand (@parent row) the task list is defined.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!