Rollup Formula

CJU · 09/09/21

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"

Genevieve P. · 09/10/21

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

Genevieve P. · 09/13/21

https://community.smartsheet.com/discussion/comment/304605#Comment_304605

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.

Genevieve P. · 09/13/21

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?

Genevieve P. · 09/14/21

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

Genevieve P. · 09/29/21

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")))

CJU · 09/29/21

Thank you

CJU · 09/29/21

You are really great. Thanks

Genevieve P. · 09/10/21

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

CJU · 09/10/21

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. · 09/10/21

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).

CJU · 09/10/21

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 · 09/10/21

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. · 09/10/21

https://community.smartsheet.com/discussion/comment/304593#Comment_304593

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")

CJU · 09/10/21

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

Genevieve P. · 09/10/21

https://community.smartsheet.com/discussion/comment/304600#Comment_304600

Perfect, thank you!

Did you try my formula above?

=COUNTIFS([Category]:[Category], <> "PARENT", Status:Status, "In Progress")

CJU · 09/10/21

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 · 09/10/21

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 · 09/10/21

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

CJU · 09/10/21

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. · 09/13/21

https://community.smartsheet.com/discussion/comment/304605#Comment_304605

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.

Genevieve P. · 09/13/21

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?

CJU · 09/13/21

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

Rollup Formula

Best Answers

Answers

Help Article Resources

Categories