Excel: SUMPRODUCT calculating shared workload in hours with percentages

user_16685655 0
Viewed 76

I'm gonna re-ask a question (Excel: SUMPRODUCT with percentages) that didn't get solved in another way:

I'm trying to calculate the workload/hours for each employee for certain projects. In column B you can see the responsible (100% of the workload), in C you can see which employee is taking 50% of the workload off the responsible employee. So I need the sum of all hours, while deducing of adding the 50% in case the workload is shared and giving it to the employee helping.

My current formula:

=SUMPRODUCT(($B$2:$B$6=$A7)*($C$2:$D$6<>"")*(1-$C$1:$D$1)*E2:E6)

enter image description here

sumproduct excel-formula excel
3 Answers

You could try:

enter image description here

Formula in D6:

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

Drag right and down.

user_97581940

You can do it with an array formula using SUMPRODUCT

enter image description here

=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

user_91998280

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)

enter image description here

user_121888610
Related