Forum Discussion

alpertakci's avatar
alpertakci
Copper Contributor
Feb 05, 2024

Distribution of Costs

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 a...
Excel
  • djclements's avatar
    djclements
    Feb 10, 2024

    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...

alpertakci's avatar
alpertakci
Copper Contributor
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.
djclements's avatar
djclements
Bronze Contributor
Feb 13, 2024
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!

Resources