Counting Number of Times a Predecessor is Used

Kelci Peterson

Hi,

Long story behind this one, I'll try to keep it short.

On Smartsheet, the critical path tool isn't working for my sheet because our project has too many hard deadlines and we are not using enough predecessors; however, we need to be able to view a critical path, so I have accepted that I will have to create one manually.  I am trying to use certain features to make this easier for me so that I do not have to do Conditional Formatting by Task Name all the time to mark the critical tasks as red in my Gantt Chart.

What I am trying to do is count how many times a particular task has been used as a predecessor in the Predecessors column.  Then I will create another column that designates "Yes" or "No" as critical path item, depending on the number of times that task was used as a predecessor.  If "yes" in that column, the task will automatically be highlighted red on Gantt Chart.

However, having a hard time with formulas.  I've tried COUNT, COUNTIF, IF, to try and generate the # of times a row number is used as predecessor, for example: =COUNTIF(Predecessors:Predecessors, 6). (for row 6).  I created a new column called "Number of Dependencies" where I have put this formula.  I would then want to create a formula in "Critical Path?" column that counts if Number of Dependencies>7, then "YES" in "critical path?" column.  Then use conditional formatting to highlight those tasks red.

But I can't get the formula to count how many times a row is used as a predecessor.  Am I messing up the format?

Thanks.

Allie

Hi Kelci,

 

I tried to use the COUNTIF function to count the number of appearances of a particular number in the Predecessors column and was also unsuccessful. My formula returned a value of 0 no matter what I tried. I believe this is because the Predecessors column contains neither text nor numeric values, but a special "Predecessors" data type that isn't being interpreted as expected by the formula.

 

I did find a way to work around this but it involved inserting a new column to my sheet. I created a new text/number column and used the formula =VALUE(Predecessors2) in each row, so the new column now contains the exact same values as the Predecessors column. The VALUE function formats them as text.

 

I was then able to use the COUNTIF formula to count the values in my new column: =COUNTIF([New Column]:[New Column]6, 6)

 

As I was thinking about this, I realized you might run into a road block using this method if you use multiple predecessors on any tasks, or if you have any Predecessors that don't have the default FS relationship. In this case the Predecessors column won't be equal to a particular number.  For example, if you had multiple Predecessors 4,5,6. on a row. This it won't get picked up in the COUNTIF formula because it's only counting rows that are equal to 6.