In this tutorial, you’ll learn how to mark tasks as complete when all subtasks are finished in Google Sheets — automatically. This is especially useful for project tracking, where each main task (or parent task) includes multiple subtasks with different statuses like Completed, Pending, or Under Progress.

We’ll walk through a practical example using formulas that dynamically update the task status based on its subtasks. By the end, you’ll have a system that marks a task as ✅ Completed only when all its subtasks are finished — no manual tracking required.

Sample Data

Our sample dataset includes:

• Column A: Task
• Column B: Subtask
• Column C: Status

Here’s how it looks:

We have two tasks:

• Task #: 1: Cable Laying with 6 subtasks – all marked as Completed
• Task #: 2: Panel Erection with 3 subtasks – not all are completed

So, the expected result is:

Let’s create a formula to mark each task as complete when all its subtasks are finished.

Formula to Mark a Task as Complete When All Subtasks Are Finished

Here’s the formula that generates the task list along with a dynamic status column:

=ArrayFormula(
   LET(
      uq_tasks, TOCOL(UNIQUE(A2:A), 1), 
      completion, COUNTIF(A2:A, uq_tasks) = COUNTIFS(A2:A, uq_tasks, C2:C, "Completed"), 
      HSTACK(uq_tasks, completion)
   )
)

This will return a two-column array: one with unique tasks, and another showing TRUE (if all subtasks are complete) or FALSE (if not).

To make it visual:

• Select the result column (with TRUE/FALSE)
• Go to Insert > Tick box to format them as tick boxes.

Update Formula for Your Dataset

Make sure to adjust the ranges in the formula according to your data layout:

• A2:A: Your Task column
• C2:C: Your Status column

Formula Breakdown

Let’s break down how the formula works:

• TOCOL(UNIQUE(A2:A), 1)
  Extracts a vertical list of unique parent tasks, excluding blanks.
• COUNTIF(A2:A, uq_tasks)
  Counts how many times each task appears (i.e., number of subtasks).
• COUNTIFS(A2:A, uq_tasks, C2:C, "Completed")
  Counts how many subtasks are marked “Completed” for each task.
• We then compare the two counts.
  If all subtasks are completed, the result is TRUE; otherwise, it’s FALSE.
• HSTACK(uq_tasks, completion)
  Combines the task list with the corresponding completion status into a two-column array.

That’s how you can mark tasks as complete when all subtasks are finished in Google Sheets — automatically and dynamically.

Resources

Want to expand your task management system? Check out these related tutorials:

• Round-Robin Task Assignment in Google Sheets (No Scripts)
• Split a Task in Custom Gantt Chart in Google Sheets
• Count Completed Tasks in Google Sheets
• Interactive Random Task Assigner in Google Sheets
• Track Task Duration, Remaining, and Elapsed Days
• Track Remaining Days in Tasks with Sparkline Gantt Charts