I am working on a progress tracker with Google sheets. I have 2 tables as in below screenshot. On the right table, the number of subtasks per each task must be auto-populated based on data from left table.

I'm looking for the expression that would somehow calculate the subtask count with the ensemble below. The approach I'm thinking is to - for every subtask, locate the last non-empty task_id cell so far, which can be used for aggregation.

What I've tried:

The temporary workaround I did is to add task_id for all subtasks and a countif formula for count

=ArrayFormula(if(isblank(D2:D), "", countifs(B2:B, "<>", A2:A, "="&D2:D)))

It works but I don't feel like repeating the task_id for every subtask since its a chore.

Thanks in Advance!

CodePudding user response：

Clear contents of range D:E and try in D1

=query(ArrayFormula(if(isblank(B2:B),, 
  vlookup(row(A2:A),
   filter({row(A2:A),A2:A},len(A2:A)),2))), 
 "Select Col1, Count(Col1) where Col1 is not null group by Col1 label Col1 
 'task_id', Count(Col1) 'Subtask count'", 0)

and see if that works?