Sum contracted hours once across multiple rows

Question

I have a table that has employees booked annual leave. In this table are the employees contracted hours based on roles.

In the column on the right, it is totaling all the hours for Jane Doe for each role.

=SUMIFS([Contract hours],[Full Name],[@[Full Name]],[Role],[@Role])

What I would like is that it only sums the role once and add the two values together so the value in 'Total Contracted Hrs' is 40

Answer 1

Here/screenshots refer:

Version 1 -

Pre-req: Office 365 compatible version Excel

=SUM(UNIQUE(FILTER($B$2:$C$5,--($A$2:$A$5=A2))))

note: EDIT - in general its impossible to create array / spill using sum - thus have to 'bullet' fixed references, and drag down (not sure you can create spill eqn. in table anyways; notwithstanding, above still applicable --)

Version 2 -

Any version

In table

=SUM(INDEX($I$2:$L$5,0,MATCH(A2,$I$1:$L$1,0)))

Interim lookup

=AVERAGEIFS($C$2:$C$5,$B$2:$B$5,H2,$A$2:$A$5,I$1)

Note: can drag this equation down/across table as req.