Summary
To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTIF function and the COUNTA function. In the example shown, the formula in F5 is:
=COUNTIF(C5:C11,"Done")/COUNTA(B5:B11)
which returns 57%, since 4 of 7 tasks are done. When a task's cell is empty until the task is complete, you can use COUNTA for both counts, as explained below.
Generic formula
=COUNTIF(status,"Done")/COUNTA(tasks)
Explanation
In this example, each task has a status in column C, and a task is considered complete when its status is "Done". The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage.
Count completed tasks with COUNTIF
At the core, this formula simply divides tasks complete by the total task count:
=complete/total
which is then formatted as a percentage. To count completed tasks, we use the COUNTIF function to count cells in C5:C11 that contain "Done":
=COUNTIF(C5:C11,"Done") // returns 4
COUNTIF is not case-sensitive, so "done" counts too. To count total tasks, we count non-blank cells in the range B5:B11 with the COUNTA function:
=COUNTA(B5:B11) // returns 7
Unlike the COUNT function, which counts only numeric values, COUNTA will count cells that include numbers or text. After COUNTIF and COUNTA run, we can simplify the formula to:
=4/7 // returns 0.571428571428571
Four divided by 7 results in the decimal number 0.571428571428571, which is displayed as 57% once the Percentage number format is applied.
When any entry means done
Some task lists leave a cell empty until a task is complete, then fill it in, for example with "Done" or a completion date. In that case, you can count non-blank cells in column C with COUNTA:
=COUNTA(C5:C11)/COUNTA(B5:B11)
In the worksheet above, four tasks have a completion date, so the result is again 4/7, or 57%. However, this version counts any entry as complete. On the status list in the first example, it would count "In progress" and "Not started" too, and return 100%. If your list has more than one status, use COUNTIF.
