Formula COUNTIF(Children) with multiple conditions

Stephane

In Smartsheet, there is a function called Countif(CHILDREN([reference])). This is a great formula but what if I wanted the count of children based on more than 1 condition?

I searched in the knowledge base, but couldn't find either a 'countifs(children)' formula nor a workaround.

Is there a workaround?

Example

In this example, today's date is 15/01/2020

If you apply the formula CountIf(Children([Complete %])), the number of children would equal 6.

What I would like to count are the children which are not 100% completed before today's date. 

So a countif based on 2 conditions. In this case I should have a count of 1 child.

Best regards

Stéphane

Genevieve P.

Hi @Stephane

You can do this with a COUNTIFS (plural), to add in multiple criteria. Try this:

=COUNTIFS(CHILDREN([End Date]@row), <TODAY(), CHILDREN([Complete %]@row), <>1)

This will look for all the children that have an end-date that is in the past, and all the children that are not 100% complete. You will want to put this in a new column on each Parent row to calculate the count of the rows below it.

If you wanted a total of all of the children, under different parents, you could them SUM that whole column with the COUNTIFS formulas.

Here are the Help Center articles I used:

• COUNTIFS function
• CHILDREN function
• TODAY function

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Stephane

You can do this with a COUNTIFS (plural), to add in multiple criteria. Try this:

=COUNTIFS(CHILDREN([End Date]@row), <TODAY(), CHILDREN([Complete %]@row), <>1)

This will look for all the children that have an end-date that is in the past, and all the children that are not 100% complete. You will want to put this in a new column on each Parent row to calculate the count of the rows below it.

If you wanted a total of all of the children, under different parents, you could them SUM that whole column with the COUNTIFS formulas.

Here are the Help Center articles I used:

• COUNTIFS function
• CHILDREN function
• TODAY function

Let me know if this works for you!

Cheers,

Genevieve

Stephane

Hi Genevieve,

Thank you for this great insight. It is very helpfull in solving my query.

Kr

Stéphane

Genevieve P.

Great! Glad I could help.