Forum Discussion
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 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!
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 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...
- 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.
- 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!
