Tracking weighted project completion without errors

Schraff S 0 Reputation points
2026-11-19T12:51:41.5333333+00:00

I'm using Excel to track multiple projects, but not every task contributes equally to project completion. Each task has:

  • A project name
  • A status such as Not Started, In Progress, Completed, or Blocked
  • An estimated number of hours used as the task weight

A completed task should contribute 100% of its estimated hours, an in-progress task should contribute 50%, and a not-started or blocked task should contribute 0%. For example, Website Redesign may have ten tasks totaling 120 estimated hours, while Mobile App may have fifteen tasks totaling 200 estimated hours.

I need to calculate the weighted completion percentage for each project: Weighted completed hours / Total estimated hours

The calculation must also handle these cases:

  • A project has no tasks yet.
  • Estimated-hours cells are blank or contain 0.
  • New tasks are added to the source table.
  • Task statuses contain extra spaces or inconsistent capitalization.
  • A project has only blocked tasks.

I want the result to return "No Tasks" when the total estimated hours are zero, rather than displaying #DIV/0!.

The source data is stored in an Excel Table named Tasks, with the columns Project, Status, and Estimated Hours. The summary sheet contains the project name in cell A2.

What formula can calculate the weighted completion percentage dynamically without helper columns?

Microsoft 365 and Office | Excel | For business | Windows

1 answer

Sort by: Oldest
  1. Igihozo Gady 255 Reputation points Independent Advisor
    2026-11-19T13:13:50.4966667+00:00

    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.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.