Countif formula if a row doesn't have any children

Is there a way to write a COUNTIF formula to count the rows that do not have any children? I would like to write a COUNTIFS formula that counts:

1. % Complete is < 1

2. Finish is in the past

3. Row doesn't have any children

I know how to write the first two. I'm struggling to write the 3rd one about children. Below is a screenshot of what I'm working with.

ancestor formula.JPG

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

Hello,

I think that you might need another column to define parents here so you can count parentless rows.

If you create a new text/number column (you can hide it later) and place a formula like this in every cell: =IF(COUNT(CHILDREN(Type@row)) > 0, 0, 1)

Where Type is the name of the column you're trying to count, you'll be able to include that in your COUNTIFS function:

=COUNTIFS([% Complete]:[% Complete], < 1, Finish:Finish, < TODAY(), Parent:Parent, 1)

brandon.elmer

That's a good idea Shaine and I might use that. The only thing is that if project managers are building/making changes to their project plans, then I will have to manually drag the formula down to the new rows. I was hoping to avoid that.

brandon.elmer

Hi Shaine,

Could the formula =IF(COUNT(CHILDREN([Task Name]5)) > 0, 0, 1) that you wrote be turned into a COUNTIF formula?

I'm trying =COUNTIF(CHILDREN([Task Name]:[Task Name]), = 0) but it returns a 0.

Any thoughts?

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?