Rollup Formula

Options
CJU
CJU ✭✭✭✭✭

I have the following Roll-Up 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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Answer ✓
    Options
  • CJU
    CJU ✭✭✭✭✭
    Answer ✓
    Options

    You are really great. Thanks

«1

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options

    Thanks, it does work. A second part of this question though; in the Project Summary Roll-up, the count is still including header / parent rows.

  • Genevieve P.
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options

    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.


  • CJU
    CJU ✭✭✭✭✭
    Options

    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.


  • Genevieve P.
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options

    The formula in the summary field is =COUNTIF(Status:Status, "In Progress") etc, for each

  • Genevieve P.
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options

    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


  • CJU
    CJU ✭✭✭✭✭
    edited 09/10/21
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options

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

  • CJU
    CJU ✭✭✭✭✭
    Options

    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 roll-up for the phase or parent within phase, will show "Not Applicable" if all sub-tasks 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"


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • CJU
    CJU ✭✭✭✭✭
    Options


    Thank you so much

    The roll-up of PP is now correct, providing the rolled-up 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!