# Formula help - count total tasks

✭✭

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.

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

• ✭✭✭✭✭✭

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.

• ✭✭

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

• ✭✭✭✭✭✭

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:

• ✭✭

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

• Employee

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: