Sumifs and array question

[Blitzkragen]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD]Value[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Title[/TD]
[TD]Hours[/TD]
[TD]Title[/TD]
[TD]Hours[/TD]
[TD]Title[/TD]
[TD]Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Justin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD]Justin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Justin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can someone help? I know this should be fairly easy, but I am struggling with it. I am looking to sum up the values in column B if a matching value to Bob or Justin appears in F:K. This is just a parse of the information and I have been able to do this with a
=Sumifs(B:B,F:F,"Justin")+Sumifs(B:B,H:H,"Justin")+Sumifs(B:B,J:J,"Justin")

but is there an easier way?

 

[sandy666]

maybe PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Project[/td][td=bgcolor:#5B9BD5]Value[/td][td=bgcolor:#5B9BD5]Title[/td][td=bgcolor:#5B9BD5]Hours[/td][td=bgcolor:#5B9BD5]Title2[/td][td=bgcolor:#5B9BD5]Hours3[/td][td=bgcolor:#5B9BD5]Title4[/td][td=bgcolor:#5B9BD5]Hours5[/td][td][/td][td=bgcolor:#70AD47]Value.1[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

15​

[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Bob[/td][td=bgcolor:#E2EFDA]

44​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]

14​

[/td][td]Justin[/td][td][/td][td][/td][td][/td][td]Bob[/td][td][/td][td][/td][td]Justin[/td][td]

47​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

15​

[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Justin[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]

18​

[/td][td]Justin[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table5
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    UnpivotOSC = Table.Unpivot(Source, {"Title", "Hours", "Title2", "Hours3", "Title4", "Hours5"}, "Attribute", "Value.1"),
    Group = Table.Group(UnpivotOSC, {"Value.1"}, {{"Sum", each List.Sum([Value]), type number}})
in
    Group[/SIZE]

 

[Tetra201]

... is there an easier way?

There is a shorter formula, but it involves a volatile OFFSET function:

=SUM(SUMIFS(B:B,OFFSET(F:F,,{0,2,4}),"Justin"))

 

[DanteAmor]

Try this

=SUMPRODUCT((F2:K5="Justin")*(B2:B5))