Count Items by a Property Looked Up for each Item-CodePudding

I have a table with a list of tasks in it. Each task can have blockers, which are other tasks in the same list, which must be completed before another can proceed. I have mocked up the table below:

So in the example, the Blocker1, Blocker2... etc columns all have data validation to only allow values taken from the Task column. The Done? column can contain "Yes" or "No". The BlockCount column is meant to count the non-Done Tasks that block each task - that's where the problem is.

I would like to count how many of the blockers have been completed for each task by looking up the value in the Done column for each task that is listed as a blocker.

My best effort so far is this formula:

{=SUM(IF(INDEX([Done?],MATCH(Task[@[Blockers1]:[Blockers5]],[Title],0))="No",1,0))}

I confirmed the formula with Ctrl Enter, trying to get the Index to work for each of the blockers, but this does not work. If I only take the MATCH() part of the formula, I successfully evaluate a range (shown below), but when I try to use that range to index different rows in the Done? column, it seems to only evaluate once, so I am confused what to do.

Can you help with a formula that will count the blockers in this way? I'm trying to list everything I need to do to fix up my house, and frankly this has started to become an time-consuming obsession that I so far have no prospect of solving!

CodePudding user response：

Try SUMPRODUCT:

=SUMPRODUCT(([Done]="No")*MMULT(--([Task]=Table2[@[Block1]:[Block5]]),ROW($A$1:$A$5)^0))

CodePudding user response：

You could approach this with COUNTIF:

=SUM(COUNTIF(Table1[@[blocker1]:[blocker5]],[task])*([done?]="no"))

If the current row [blocker1:blocker5] mention a task that appears in [task] it returns 1(TRUE), else 0 (FALSE) for each value in the range (array).

Multiplying this array against the array of 1's and 2's returns (1*1=) 1 for each row where the task is found and [done?] says no. All others return 1*0, 0*1, or 0*0 all resulting in 0.

SUM adds up the instances of blocker tasks found that mention no.

Older Excel versions need this formula entered with ctrl shift enter enter image description here