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.
Count Children if Criteria is Met
I'm trying to get the =COUNT(Children()) formula to work with a criteria. For instance, I have children that require a status of 'In Progress' or 'Completed', when the status is 'Completed', I want the Count to reflect the number still 'In Progress' as opposed to ALL the items listed below. is it possible?
Comments
-
No enhancement needed...
1) Add two hidden column
2) [HiddenColumnA]2 : =If([Status]2 = "Completed", 1, 0)
2) [HiddenColumnB]2 : =If([Status]2 = "In Progress", 1, 0)
3)in the visible column tallying items to get the number complete : =If([Status]1 = "Completed", Sum(CHILDREN([HiddenColumnA]1)) , sum(CHILDREN([HiddenColumnB]1)) )
4)in the status column of the parent row =IF(COUNT(CHILDREN(Status1)) > SUM(CHILDREN(HiddenColumnA1)), "In Progress", "Completed")
That should get you there.
-Brett
-
Ray,
Countif() should work for you.
=COUNTIF(CHILDREN(), "In Progress")
James
-
Much simpler Jay. It eliminates the need for hidden columns.
-
I've tried both options and neither of them do what I need. In the Parent Column, I currently have a formual set like, ="Departmental Task (" + COUNT(CHILDREN()) + ")" This gives the result +Departmental Task (#). The "#" reflected are all the "Children" under that parent row regardless of the status in another column. What I'm trying to accomplish is the "#" to be a count only if the "Children" below have a status in another column not equal to "complete."
-
Ray,
Make a copy of your sheet and , scrub and senstive company secrets from it and publish the web version. That way we can see the all formulas and find the bugs.
-Brett
-
Hi Ray,
This formula will subtract the number of times "Completed" is shown from the number of children. It will be useful if you have more than two status options.
="Departmental Task (" + (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Completed")) + ")"
If this is located in your Task column, leave the first CHILDREN() alone, but add a cell reference to the second CHILDREN(). For example if your parent row is row 1:
="Departmental Task (" + (COUNT(CHILDREN()) - COUNTIF(CHILDREN(Status1), "Completed")) + ")"
If you only have two possible status options, use this:
="Departmental Task (" + COUNTIF(CHILDREN(), "In Progress") + ")"
Again, you will need to referance the Status column parent cell:
="Departmental Task (" + COUNTIF(CHILDREN(Status1), "In Progress") + ")"
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives