Excel: Sum-formula

Question

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)?

This is an interesting one, keen to see what the smarter people than me come up with as a formula. — Skin
– Skin, Commented Jan 27, 2022 at 7:13
@Skin well sumifs() and divide by row sum of x comes to mind, but on smartphone so can’t test. — Solar Mike
– Solar Mike, Commented Jan 27, 2022 at 7:16
@SolarMike, yeah, but the dynamicness is what I was hoping for. The answer below works but if more people are added, the formula needs to shift, not a problem, was just hoping for it to be completely dynamic. :-) — Skin
– Skin, Commented Jan 27, 2022 at 7:47
@Skin is this your question? You seem very focused on it? Also if you change absolute to relative then you can drag that formula as far as you like so very dynamic. — Solar Mike
– Solar Mike, Commented Jan 27, 2022 at 7:51

JvdV · Accepted Answer · 2022-01-27 08:23:27Z

2

With ms365:

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:

edited Jan 27, 2022 at 8:23

answered Jan 27, 2022 at 8:01

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harun24hr · Accepted Answer · 2022-01-27 07:30:01Z

2

Give a try on below formula-

=SUMIFS($C$2:$C$4,A$2:A$4,"x")-SUMIFS($C$2:$C$4,$A$2:$A$4,"x",$B$2:$B$4,"x")/2

answered Jan 27, 2022 at 7:30

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

Excel: Sum-formula

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related