Status Formula Explanation
I am trying to figure out how to roll up a status in my project plan. I have been trying to figure it out using this link from the community: Formula to Roll Up Task Status — Smartsheet Community.
However, I am getting confused about the "helper" column. If I understand correctly, I need to have a status column where I manually enter the status. I then need a different column that will read the manually-entered statuses from the previous column, and roll them up. Is that correct? I will paste the formula I have tried below. I think the problem is that I don't have it referring to the manual column, but rather its own column. I have tried to adjust this to point to the manual column, but the formula is too complex for me and I keep breaking it when I try to figure out where the reference should go. Is anyone able to assist? I'm not picky, and will take any help I can get, but I always appreciate both assistance with the formula, and a bit of information so that hopefully I have to bug the community less over time.
=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold")))))
In the screenshot below, the manual column is "Status", and the column where I have put the formula is "Rolled Up Status". Thank you in advance
Best Answer
-
I was just about finished on your answer! The square brackets won't cause a problem. You must have them if there are spaces in the column names, but it does no harm to have them where they are not necessary.
Here is the answer to the spot-the-difference:
1) It looks like you accidentally deleted a ( when you changed the column name in one of the functions. It should be IF(COUNT(CHILDREN([Status]@row) =
2) Where you added the extra statuses you missed a closing parenthesis each time. You need to close both the OR and the COUNTIFS functions before entering the value if true for the IF.
3) You also added two more parts to the big IF (the second IF in the function) but did not close them. You need to add two more ) towards the end.
4) If you look at the (12) quotation marks around the cell values you entered you will see they are curved: “Cancelled” They should be straight: "Cancelled". Curved quotation marks will result in an "Unparseable" error. You should either type directly into smartsheet or use a simple text editor, like notepad, to prevent any "fancy" quotation marks.
Once you correct those things you will see the formula is color coded which makes it much easier to see where a parenthesis is missing or duplicated. See in the example above the formula ends with orange but starts with black - that is not a matching pair.
Here we have nice color coded pairs, and the column names are in color. Lovely!
This is not causing a problem, but I should have suggested using (CHILDREN(Status@row)) instead of (CHILDREN()) in the very first COUNT.
Final formula is as follows (changes in bold)
=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIFS(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Initiated")), "Initiated", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Blocked")), "Blocked"))))))), "Unknown")
Answers
-
The formula
The formula is looking at the child rows in the current column. If all the child rows are either something or "Cancelled" then the something is displayed.
How about I step by step explain the formula you have and we see if that is enough for you to edit it to do what you want.
It looks a lot less scary in small pieces, so here we go.
=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress",
Means, if the row has more than 0 child rows with "In Progress" in the current column then output "In Progress".
If not, move on to...
IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Cancelled"), "Cancelled",
Which means, if all of the child rows have "Cancelled" in the current column then output "Cancelled". It does this by counting the number of child rows and seeing if this is equal to the number of child rows with "Cancelled".
If this is also not true, move on to...
IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started",
Which is very similar to the last part we looked at, but has an OR in it as well. This part in bold is counting the number of child rows with Cancelled OR Not Started in the current column. So here it is going to output "Not Started" if all of the children are Cancelled or Not Started (and as the IF before has marked those where they are all Cancelled as Cancelled, this will mark those where there is at least one Not Started and the rest are either Not Started or Cancelled).
If this is also not true, the formula moves on to...
IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "Complete")), "Complete",
So here, just like above, if all children are Complete OR Cancelled then the formula outputs Complete.
If this is also not true, the formula moves on to..
IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold"
So here, just like above, if all children are Complete OR On Hold then the formula outputs On Hold.
And then there are all the closing parenthesis from all of the IFs (the one on the right goes with the first one at the start of the formula, the one on the left goes with this final IF).
There is nothing output if all of these statements above are false.
)))))
Referring to a different column
The formula above is working on the same column as the data is in. If you are manually entering the statuses then I recommend you do not put these into the same column as the formula as someone will overtype the formula. You can put the statuses into another column and then refer to that by adding the column name between the parentheses after CHILDREN().
=IF(COUNTIFS(CHILDREN([Your Column Name Here]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold")))))
And finally
A good practice for formula like this is to use an IF(COUNT(CHILDREN()) > 0, at the very start. That way you can evaluate that function only for rows with children and do something different (such as copy in the status from another place) for all the children, and thus have a single formula for the whole column.
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN([Your Column Name Here]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN([Your Column Name Here]@row)) = COUNTIFS(CHILDREN([Your Column Name Here]@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold"))))), "another formula")
-
Hi KPH,
Thank you so much for breaking it down for me. I understand it a lot better, and the formula worked for me! I got confident and tried to add in additional criteria for "Initiated" and Blocked". It didn't work. If I paste my formula below (I will bold the part I added - though I'm sure you'll recognize it) would you be able to help me? If this makes it super complex, I can leave out the additional statuses - they are nice-to-haves. Thank you again, and I'm sorry for what I'm sure is a silly and basic question for you
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN([Status]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Status]@row)) = COUNTIFS(CHILDREN([Status]@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN([Status]@row)) = COUNTIFS(CHILDREN([Status]@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN([Status]@row)) = COUNTIFS(CHILDREN([Status]@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN([Status]@row)) = COUNTIFS(CHILDREN([Status]@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold", IF(COUNT(CHILDREN[Status]@row)) = COUNTIFS(CHILDREN([Status]@row), OR(@cell = “Cancelled”, @cell = “Initiated”), “Initiated”, IF(COUNT(CHILDREN([Status]@row)) = COUNTIFS(CHILDREN([Status]@row), OR(@cell = “Cancelled”, @cell = “Blocked”), “Blocked”))))), "Unknown")
Clearly this is returning an "Unparseable" result
-
You are so close @juwilson .
I was going to give you a clue so you could find the issue yourself but actually found 4 things, 2 of which aren’t your fault, so that seemed unfair. I want to explain them but am on my cell phone and don’t want to leave you stuck. So here is the fixed formula. I will explain tomorrow when I’m at my desk. Unless you beat me to it!
=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIFS(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Initiated")), "Initiated", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Blocked")), "Blocked"))))))), "Unknown")
-
@KPH maybe this is a silly observation, but I think that one of my mistakes was leaving the square brackets in? Even after you had just explained that it was unnecessary.
-
I was just about finished on your answer! The square brackets won't cause a problem. You must have them if there are spaces in the column names, but it does no harm to have them where they are not necessary.
Here is the answer to the spot-the-difference:
1) It looks like you accidentally deleted a ( when you changed the column name in one of the functions. It should be IF(COUNT(CHILDREN([Status]@row) =
2) Where you added the extra statuses you missed a closing parenthesis each time. You need to close both the OR and the COUNTIFS functions before entering the value if true for the IF.
3) You also added two more parts to the big IF (the second IF in the function) but did not close them. You need to add two more ) towards the end.
4) If you look at the (12) quotation marks around the cell values you entered you will see they are curved: “Cancelled” They should be straight: "Cancelled". Curved quotation marks will result in an "Unparseable" error. You should either type directly into smartsheet or use a simple text editor, like notepad, to prevent any "fancy" quotation marks.
Once you correct those things you will see the formula is color coded which makes it much easier to see where a parenthesis is missing or duplicated. See in the example above the formula ends with orange but starts with black - that is not a matching pair.
Here we have nice color coded pairs, and the column names are in color. Lovely!
This is not causing a problem, but I should have suggested using (CHILDREN(Status@row)) instead of (CHILDREN()) in the very first COUNT.
Final formula is as follows (changes in bold)
=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIFS(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), "Cancelled"), "Cancelled", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Not Started")), "Not Started", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Complete")), "Complete", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "On Hold")), "On Hold", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Initiated")), "Initiated", IF(COUNT(CHILDREN(Status@row)) = COUNTIFS(CHILDREN(Status@row), OR(@cell = "Cancelled", @cell = "Blocked")), "Blocked"))))))), "Unknown")
-
@KPH Thank you so much! I feel very silly, and am very grateful for your help
-
Not silly. You tried something new. You almost got it. And now you know what to look for you will get it right next time.
Help Article Resources
Categories
Check out the Formula Handbook template!
