#### 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)

Options
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

• Options

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

• Options

Good Idea!!

Thank You Very much!

• ✭✭✭✭✭✭
edited 03/31/16
Options

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

• Options

Hi Craig

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

• ✭✭✭✭✭✭
Options

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.

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 09/06/18
Options

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)

Craig

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.