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.
Comments
-
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)
-
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.
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!