Excel: SUMPRODUCT calculating shared workload in hours with percentages-CodePudding

I'm gonna re-ask a question (

CodePudding user response：

Another option:

=SUMPRODUCT(($A6=$B$2:$B$5)*($B$1-$C$1*(""<>$C$2:$C$5))*D$2:D$5 ($A6=$C$2:$C$5)*$C$1*D$2:D$5)

CodePudding user response：

You can do it with an array formula using SUMPRODUCT

=SUMPRODUCT(--($B$2:$B$5=$A8)*IF($C$2:$C$5="";D$2:D$5;D$2:D$5/2)) SUMPRODUCT(--($C$2:$C$5=$A8)*D$2:D$5)*0,5

Because it's an array formula, it must be entered pressing CTRL SHIFT ENTER

CodePudding user response：

You could try:

Formula in D6:

=SUMPRODUCT(($B$2:$C$5=$A6)*D$2:D$5/MMULT(--($B$2:$C$5<>""),{1;1}))

Drag right and down.