What would be the formula to only count parent row items with the "in progress" statuses

Rushi Patel

Hello Everyone,

I am new to Smartsheet and I am struggling with identifying a Smartsheet formula for one of my sheet. I am attaching an example image for reference.

Here, I have three columns with headings "Only Projects", "Project Name", "Status".
I would like my "Only Projects" column to display the value "True" against those projects that are at the parent level (i.e. in this case A, C & F are parent row) and projects that does not have any children (i.e. B, D & E) and are at its main level and its status is "in progress".

Would appreciate the help in regards to this.

Thank you!

AKnight

Hi @Rushi Patel !

You will need to first make two helper columns, one for level and one for counting children. For level, use the formula =COUNT(ANCESTORS()) . For counting children, use the formula =COUNT(CHILDREN()) . From there we can do a IF AND formula (see below) using column names for level as "HELPER - Level" and for counting children as "HELPER - Children"

=IF(AND([HELPER - Level]@row = 0, [HELPER - Children]@row = 0, Status@row = "In Progress"), "True", "False")

The above formula is if all criteria needs to be met; however if you just need one piece of criteria met use OR instead of AND.

Hope this helps!

Andrée Starå

https://community.smartsheet.com/discussion/133903/what-would-be-the-formula-to-only-count-parent-row-items-with-the-in-progress-statuses

Hi,

I hope you're well and safe!

Here's another option I always use in my client solutions.

Add a helper column called Level using the formula below. You can then use it to identify parents, use it for conditional formatting, and more.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Rushi Patel

Hello @AKnight,

Firstly thank you

Rushi Patel

Hello AKnight,

Firstly thank you for providing help on this. However, I am not sure I understand correctly and is giving me the right solution.

Let me put in simple form, I want the value "True" to appear against all projects that are "In Progress", and projects that are Parent Row and projects that is not the parent row (which means does not have any children).
So value "True" should appear as per the image attached against Project Name A, B and C (A and C has children. B does not have any children).

Thank you!

Rushi Patel

Hello Andree,

Thank you for your help. However, I tried the formula but its not working.

Let me put in simple form, I want the value "True" to appear against all the projects that are "In Progress", and projects that are Parent Row and projects that is not the parent row (which means does not have any children).

So value "True" should appear as per the image attached against Project Name A, B and C (A and C has children. B does not have any children).

Thank you!

Michelle Choate 2

Then it would be a simple modification of @AKnight's formula.

=IF(AND([HELPER - Level]@row = 0, Status@row = "In Progress"), "True", "False")