Distribution of Costs

[alpertakci]

I have a list of employees whose cost of employment items are separated (coded in the attached Excel file (D7:R7).

I have a distribution key where some departments absorb the cost of the others and their own. (C16:M26). (Departments G, H, and I bear no cost).

What I need to calculate is the sum of all the costs per employment cost item (C31:C:45) for a given department using the distribution key.

Thanks in advance for all your support!

[#2 · Accepted solution]

@alpertakci Here's one option using XLOOKUP and SUMIFS. In cell D31, use the following formula, then copy it down and across for each P&L Item and Department:

=SUM(
    XLOOKUP($C31, $D$7:$R$7, $D$8:$R$11)*
    SUMIFS(
        XLOOKUP(D$30, Table3[Department Code], Table3[[A]:[I]]),
        Table3[[#Headers],[A]:[I]],
        $C$8:$C$11
    )
)

Note: If you're using an older version of Excel, XLOOKUP can be replaced with INDEX/MATCH and SUM can be replaced with SUMPRODUCT.

Please see the attached workbook...

[#3]

This works super for me. Thanks very much, and sorry for the late reply, as the weekend and the Monday rush got in the way.

[#4]

No worries... your question sat unanswered for almost a week, so we should be the ones apologizing. 😉 I'm happy to hear it worked out for you. Cheers!