# Counting Children Tasks with specific criteria

Options
edited 11/02/20

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.

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?

• ✭✭✭✭
Options

Hi,

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

• Employee
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

October 8 - 10, Seattle, WA | Register now

• Options

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)

Brand (A)

Brand (B)

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

Does this help?

• ✭✭✭✭✭
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.

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)

• Options

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!