Project Status Formula Help

Laurenzella

I have a formula I need help with for the top level of my project checklist hierarchy.

1. Overall Project Status - this is looking only at the highest level rows which are indicated by the helper column Hierarchy=0.
   1. The objective of the formula is as follows:
      1. If the Project Override field (in the Sheet Summary) is filled out = Display that value
      2. If the Project Override field is blank - then look at all parent rows where the following is true:
         1. "Default Filter" column is checked AND
         2. "Summary" column is not checked AND
         3. "Hierarchy" column = 0 THEN
         4. Look at "Status" column
            1. If all the rows say "Completed" > Project Status: Complete
            2. If all rows say "Not Started" > Project Status: Planning
            3. If any row says "In Progress" > Project Status: Active

This is the current formula we have that does not appear to be working:

Formula =IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, OR(@cell="Completed", @cell="Cancelled")) = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

prime_nathaniel

Laurenzella

I am pretty sure you have some incomplete syntax in here too, I just tried to prettize your formula and the parenthesis and parameters don't look quite right. Either way, what you are trying to do is,

// This part is correct

=IF([Project Status Override]# <> "", [Project Status Override]#, $calculated_value)

$calculated_value you have

IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, OR(@cell="Completed", @cell="Cancelled")) = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active"))

// the and() formula is normally and(criteria1, criteria2) so yours is currently reading as

COUNTIFS([Tasks Needed]: [Tasks Needed], 0, Summary: Summary, 0, Hierarchy: Hierarchy, > 0, Status: Status, OR(@cell = "Completed", @cell = "Cancelled")) = COUNTIFS([Tasks Needed]: [Tasks Needed], 0, Summary: Summary, 0, Hierarchy: Hierarchy, > 0)

// Which is all just one item so either and is not needed or perhaps there should be a , somewhere in there.

Okay so then to accomplish what I think you are looking for,

Total rows =
"Default Filter" column is checked AND
"Summary" column is not checked AND
"Hierarchy" column = 0 THEN

If all the rows say "Completed" > Project Status: Complete
If all rows say "Not Started" > Project Status: Planning
If any row says "In Progress" > Project Status: Active

$total_number of rows = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0)

$complete_rows =

not $not_started_rows = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status,"Not Started")

in $progress_rows = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status, "In Progress")

Now to put that all together you want to consider your if then structure,

If($complete_rows = $total_rows, "Project Status: Complete", if($not_started_rows = $total_rows, "Project Status: Planning", "Project Status: Active")))

// You will notice that as there are only 3 options for status if it is not complete and it is not planning it has to be active.

So now we just swap in all our formulas, all we are doing is comparing numbers vs numbers.

$calculated_value =

If(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status, "Completed") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0), "Project Status: Complete", if(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status, "In Progress") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0), "Project Status: Planning", "Project Status: Active")))

Great, now we have status calculated, so we can put that whole thing into our original override formula

=IF([Project Status Override]# <> "", [Project Status Override]#, $calculated_value)

and we get

=IF([Project Status Override]# <> "", [Project Status Override]#, If(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status, "Completed") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0), "Project Status: Complete", if(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0, Status:Status, "In Progress") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], true, [Hierarchy]:[Hierarchy], 0), "Project Status: Planning", "Project Status: Active"))))

// Side note, if it is easier for you to visualize you can use collect then countif to get the total rows eligible for analysis. For anything complex like this I recommend you put the formula together piece by piece using helper columns first, then combine them altogether after you have verified each component piece is what you are looking for. When it comes to nesting if() you will want to report back the test case as "pass", "fail" so you know exactly what component goes where. Additionally, when nesting it is best to always write out the full if formula to ensure you do not miss a parenthesis or comma

if(true, "pass","if(true, "pass", "fail"))

You can also choose to write these indented in a text editor more like normal code then remove the white space later on.

Laurenzella

I tried pasting in the formula you supplied below but it returns an #unparseable error. Also (it didn't work either way) but I changed the "true" for the summary column to be "false" because I believe true=checked and false=unchecked but let me know if I am wrong.

=IF([Project Status Override]# <> "", [Project Status Override]#, If(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], false, [Hierarchy]:[Hierarchy], 0, Status:Status, "Completed") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], false, [Hierarchy]:[Hierarchy], 0), "Project Status: Complete", if(COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], false, [Hierarchy]:[Hierarchy], 0, Status:Status, "In Progress") = COUNTIFS([Default Filter]:[Default Filter], true, [Summary]:[Summary], false, [Hierarchy]:[Hierarchy], 0), "Project Status: Planning", "Project Status: Active"))))