Search code examples
excelexcel-formulacountsum

Excel: Sum-formula

I hope you can help me with the following. I want to calculate the amount of work for each person in the left table. If one person works on the task, he'll need the full amount of hours. If two people work on the job, the hrs of work will be divided by the two.

So it should end up with 13 hrs for person A and 15 hrs for person B. Who can help me with capturing this in a single formula per person (so not adding multiple columns with sub calculations)?

enter image description here

Solution

  • With ms365:

    enter image description here

    Formula in E3:

    =SUM(FILTER($C2:$C4/LEN($A2:$A4&$B2:$B4),A2:A4="x"))

    More dynamic:

    =SUM(FILTER($C2:$C4/MMULT(--($A2:$B4="x"),SEQUENCE(COUNTA($A1:$B1),,,0)),A2:A4="x"))

    Drag both options to the right

    Most dynamic I can think of right now, this time in E2:

    =CHOOSE({1\2},A1:B1,BYCOL(A2:B4,LAMBDA(y,SUM(IF(y="x",C2:C4/BYROW(A2:B4,LAMBDA(x,COUNTIF(x,"x"))),0)))))

    This formula will spill all workers and their hours:

    enter image description here