SOLVED

Distribution of Costs

Copper Contributor

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.

3 Replies
best response confirmed by alpertakci (Copper Contributor)
Solution

Re: Distribution of Costs

@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]]),
\$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.

Re: Distribution of Costs

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.

Re: Distribution of Costs

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!
1 best response

Accepted Solutions
best response confirmed by alpertakci (Copper Contributor)
Solution

Re: Distribution of Costs

@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]]),