Countif(Children(), Criteria)

Irene McGregor

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

 

Jenny Lam

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") + ")"

 

Irene McGregor

Good Idea!!

 

Thank You Very much!

J. Craig Williams

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

Irene McGregor

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 Williams

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

 

 

 

Mixer

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 Williams

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

 

 

Mixer

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 Williams

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

Mixer

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 Williams

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