Rollup Formula
I have the following RollUp formula for Status
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))
I want to add a status called "Not Applicable"  since not all line items off the my template PP apply to all projects.
What would the formula be in adding this additional status  and for roll up to remain accurate?
What would the master  top line  roll up formula be, so it would essentially ignore any "Not Applicable"
Best Answers

Hi @CJU
Try adding this criteria to the COUNT that has to do with the overall Children, so we can exclude "Not Applicable" from that count.
Like so:
=IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))
Let me know if this gives you the desired result.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
My apologies! I missed that the row which says "Parent" could also potentially be blank. Yes, we can include that in.
It's just a matter of telling the Category column what to search for. We want to see if the cell is "", but also, if the cell is NOT the "parent":
=COUNTIFS(Status:Status, "In Progress", [Category]:[Category], OR(@cell = "", @cell <> "PARENT"))
You should just need to change out the Status that you're looking for in the quotes.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
For your newest formula question, we can also add in the logic to look and see if all Children are "Not Applicable" like so:
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started")))
It's the same statement as when you look for all the "Complete" children.
Are there any other adjustments you need to make?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Good morning, @CJU !
Can you copy/paste the exact formula you're using for the Not Applicable rollup field?
ex:
=COUNTIFS(Status:Status, "Not Applicable", [Category]:[Category], OR(@cell = "", @cell <> "PARENT"))
If the text in quotes is even one character different from what's in your sheet you'll receive a Count of 0. Is it possible there's a small typo?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Of course! The order of operations is really important. We just need to change this around.
Put the "Not Applicable" first.
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")))
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you

You are really great. Thanks
Answers

Hi @CJU
Try adding this criteria to the COUNT that has to do with the overall Children, so we can exclude "Not Applicable" from that count.
Like so:
=IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))
Let me know if this gives you the desired result.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks, it does work. A second part of this question though; in the Project Summary Rollup, the count is still including header / parent rows.

Hi @CJU
If there's a helper column in your sheet which identifies Parent rows vs Children, we can use this as a filter for rows to exclude in the formula.
Can you post a screen capture of what you're referring to? (But please block out sensitive data).
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
In the project Summary you don't have option to filter. It's just that automatic formula. You see there are 33 lines so it is counting the header / parent lines.

If we can filter here, I do have a helper column. I have assigned the header / parent with PARENT. I would like these excluded from the count.

Hi @CJU
My apologies for not being clear  what is the formula in that Summary Field? We can add the criteria of the Category NOT being "Parent" within that formula in order to "filter out" those rows inside the formula.
Ex:
=COUNTIFS([Category]:[Category], <> "PARENT", Status:Status, "Not Started")
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
The formula in the summary field is =COUNTIF(Status:Status, "In Progress") etc, for each

Perfect, thank you!
Did you try my formula above?
=COUNTIFS([Category]:[Category], <> "PARENT", Status:Status, "In Progress")
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
No it did not work
I changed line 3 to in progress and the "In Progress" count formula, as you have above, but the result is 0, but should be 1. It should count line 3

It works if the Category is not Blank; i.e. I added a value to line 3 and the formula now works, counting line 3, but not its parent

Is there a way to avoid this, in case a line does not have a category?

Hi
Essentially there are three formula required
Sheet Summary
=COUNTIFS(Category:Category, <>"PARENT", Status:Status, "In Progress")
This works but only if Category is not blank. How can it be modified to not consider Blank?
Top task / Header (line 1)
=IF(COUNTIF(CHILDREN(), <>"Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))
This works and counts all tasks excluding the phase or parent. No change is required.
Phase and parent (within phase)
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))
What do I need to add to include "Not Applicable" so the rollup for the phase or parent within phase, will show "Not Applicable" if all subtasks below this parent, are all "Not Applicable". The formula currently works for the other 3 Status
e.g. below, line 4 is a parent; the 2 children (lines 5 and 6) are both Not Applicable, so the roll up for this task (line 4) should also be "Not Applicable"

My apologies! I missed that the row which says "Parent" could also potentially be blank. Yes, we can include that in.
It's just a matter of telling the Category column what to search for. We want to see if the cell is "", but also, if the cell is NOT the "parent":
=COUNTIFS(Status:Status, "In Progress", [Category]:[Category], OR(@cell = "", @cell <> "PARENT"))
You should just need to change out the Status that you're looking for in the quotes.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
For your newest formula question, we can also add in the logic to look and see if all Children are "Not Applicable" like so:
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started")))
It's the same statement as when you look for all the "Complete" children.
Are there any other adjustments you need to make?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you so much
The rollup of PP is now correct, providing the rolledup status.
The counter in Project Summary still has some issues:
I have a "Not Applicable Counter". With the updated formula it is not counting "Not Applicable". When I change a line to "Not Applicable" the count is not shown; although the "Not Started" count goes down correctly.
e.g. below  I change line 6. You will see in image 2, the fx of "Not Applicable" in the Project Summary stays at 0. The fx of "Not Started" does correctly got from 24 to 23.
Image 1
Image 2
Help Article Resources
Categories
Check out the Formula Handbook template!