A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hello Schraff,
You can use LET together with SUMPRODUCT to calculate weighted project completion without helper columns.
Since your source data is already stored in an Excel Table named Tasks, enter the following formula in your summary sheet next to the project name in A2:
excel
=LET(
projects,Tasks[Project],
hours,IFERROR(--Tasks[Estimated Hours],0),
statuses,LOWER(TRIM(SUBSTITUTE(Tasks[Status],CHAR(160)," "))),
total,SUMPRODUCT((projects=A2)*hours),
weighted,SUMPRODUCT((projects=A2)hours
((statuses="completed")+0.5*(statuses="in progress"))),
IF(total=0,"No Tasks",weighted/total)
)
This formula:
- Counts completed tasks at 100%, in-progress tasks at 50%, and all other statuses at 0%.
- Uses estimated hours as weights.
- Handles extra spaces and inconsistent capitalization in statuses.
- Treats blank or zero estimated hours as zero.
- Returns No Tasks when the project's total estimated hours are zero.
- Automatically includes new tasks added to the Tasks table.
After entering the formula, format the result cell as Percentage and copy it down for the other projects.
You can also refer to Microsoft's documentation: Using structured references with Excel tables.
I hope this helps you track your project completion accurately.