Essentially I am trying to find the sum in column A that corresponds to names distributed in the array to the right. Below is a picture of what I am trying to do and what my formula is as of now. Please let me know either how to modify my formula or how to restructure my data.

CodePudding user response：

Here is a formula I came up with that can accomplish this:

=sum(filter($A$2:$A$11, arrayformula(REGEXmatch(ARRAYFORMULA((IF(ROW($A$2:$A$11)=1,"formulaTest",$B$2:$B$11 & $C$2:$C$11 & $D$2:$D$11 & $E$2:$E$11 & $F$2:$F$11))),B13))=TRUE))

This formula should be placed in the cell below Name1, next to Totals, and then copied across the row under each name. The cell references should be changed to match your sheet/needs. Anything with $ before it defines either the duration column, or each column that contains the names. The last cell reference (B13) is a reference to the cell that contains the name you are looking to total. A screenshot of an example of this is shown at the bottom of this answer.

In simple terms, this formula combines the names from each row into a single string (per row), searches for each name within that combined string, filters the duration value if there is a match, and then sums that value.

Hope this helped! Let me know if there is anything I should explain further or better clarify.