Formula help - count total tasks

Cecilie

Hi everyone,

We have a project plan sheet for each of our projects containing a long list of tasks. My predecessor made 3 columns for counting number of Total Tasks, Tasks Completed, and Tasks Overdue.

I recently made some graphs using this data and noticed that the numbers were off, and I need some help in changing the formulas.

It seems that the count for 'Task Completed' dosen't atually count if the checkbox for 'Task Complete' is checked, but rather triggers if the checkbox is checked and today is past the deadline, which seems redundant. Additionally, I feel the other formulas can be simplified: maybe Total Tasks could be a single formula, instead of running in each cell? I also hope to avoid using TODAY altogether, as it ruins the data in the column checking for changes.

The current formulas are as follows and are in each cell down the columns.

Tasks Total: =COUNT([Task Complete?]164:[Task Complete?]164)

Completed tasks: =COUNTIFS(Finish164:Finish164; <TODAY(); [Task Complete?]164:[Task Complete?]164; 1)

Tasks overdue: =COUNTIFS(Finish164:Finish164; <TODAY(); [Task Complete?]164:[Task Complete?]164; 0)

So, to sum up, can anyone help me with how to write these 3 so they are more optimized to the above? 😊

Nick Korna

I would guess that your predecessor was using a SUM on these columns for graphing purposes (possibly in the sheet summary?)

In terms of making them not by line you could switch them to COUNTIFS over column ranges, but then the question would be if you want to include the parent lines as being counted as tasks or not.

Cecilie

@Nick Korna Yes, exactly (:

Would it be possible to substract the parent lines? They're always the same amount of lines, while the list of children can vary.

One of the issues right now is that new tasks might be added without the right the funtionality - so if the COUNTIF got all of them if would make it easier.

Nick Korna

It would be possible, but the exact formula to do it is going to depend on how your tasks are laid out - do you have tasks with subtasks that you would want counting? E.g. would it be more like column 2 or 3 (Y is to count) below:

Cecilie

@Nick Korna

It would be like column 2 - there are about 9 Task headers under a main parent.

Genevieve P.

Hey @Cecilie

What I would do is have a helper column that counts if there are any child rows or not. If there are child rows, add a marker so that the row will be excluded:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent", "")

Now that we have a marker for the Parent rows, we can exclude them from your Counts:

=COUNTIFS([Task Complete?]:[Task Complete?]; 1; Helper:Helper; <> "Parent")

=COUNTIFS(Finish:Finish; <TODAY(); [Task Complete?]:[Task Complete?]; 1; Helper:Helper; <> "Parent")

=COUNTIFS(Finish:Finish; <TODAY(); [Task Complete?]:[Task Complete?]; 0; Helper:Helper; <> "Parent")

Does that make sense?