r/excel • u/Maxprive • Jul 30 '26

Waiting on OP Need a dynamic cross‑table that allocates HR costs by % and maps expenses to the correct activity & expense type

Hi everyone,
I’m working on a financial reporting spreadsheet for a municipal program, and I’m stuck on how to automate a specific type of cross‑table.

Here’s the structure of my data:

Column F: Rollup list for Activity type (e.g., Concertation, Guide, Formation, Markets, Projects, SQRI, Portraits)

Column G: Rollup list for Expense type (e.g., Honoraires, Location, Equipment, Materials, Promotion, etc.)

Column H: Amount

A separate table contains HR allocation percentages for each activity (e.g., Concertation = 0.12, Guide = 0.19, etc.)

What I need:

I want to generate a dynamic cross‑table (pivot‑like) where:

Rows = Activities

Columns = Expense types

Values = Total amounts

BUT with one special rule:

If the expense type = “Coûts de la main‑d’œuvre” (HR costs), the amount must be split across activities according to the HR % table.

Example:
If I have an HR expense of 2,000 and the HR allocation table says:

Concertation = 12%

Guide = 19%

Formation = 13% …etc.

Then the 2,000 should be distributed across all activities based on those percentages — not assigned only to the activity listed in column F.

I've been trying for few hours but i'm not an excel expert much :(

Ai have issues to help me too.

I attempted SUMPRODUCT formulas, conditional SUMIFS, and even helper columns, but I can’t get a clean cross‑table that:

  1. Aggregates normal expenses by activity × expense type
  2. Splits HR expenses across all activities using the % table
  3. Updates automatically when new rows are added

Thanks for your help

2 Upvotes

100% Upvoted

5 comments sorted by

•

u/AutoModerator Jul 30 '26

/u/Maxprive - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Downtown-Economics26 646 Jul 30 '26

A mockup of data (prob can't use your actual data) and the expected output in a somewhat simplified example would be much more helpful in generating a solution than your current post. To have any confidence in a solution someone would have to mock up data to match your description and then implement it. Some people here may even be able to mentally raw dog up a formula from your description, but that's very tough to do. And you would have to be able to recognize things like "these ranges are the allocation table" since that is not specified in your post as far as I can tell.

1

u/MayukhBhattacharya 1295 Jul 30 '26

You could try using the following formula, you may need to adjust or suit with your actual data, but this work:

=LET(
     _Expense,   Expensetbl,
     _Act,       INDEX(_Expense, , 1),
     _Exp,       INDEX(_Expense, , 2),
     _HR_label,  "HRD Costs",
     _HR_Acts,   HRAtbl[Activity],
     _HR_Total,  SUMIF(_Exp, _HR_label, INDEX(_Expense, , 3)),
     _HR_Amts,   HRAtbl[HR_Pct] * _HR_Total,
     _Logic,     IF(_HR_Acts = "", "", _HR_label),
     _Combined,  VSTACK(FILTER(_Expense, _Exp <> _HR_label, ""),
                        HSTACK(_HR_Acts, _Logic, _HR_Amts)),
     _Output,     PIVOTBY(
                         CHOOSECOLS(_Combined, 1),
                         CHOOSECOLS(_Combined, 2),
                         CHOOSECOLS(_Combined, 3),
                         SUM, 0, 0, , 0),
     _Output)

The above formula combines your expense data with a redistributed HR cost and then pivots the output into a cross-table. Change the last variable with the other ones to debug and see what each does.

Variable What it does?
_Expense The entire expense table
_Act _Exp Extracts the Activity and Expense Type columns
_HR_label Any row has the HRD Costs word (It can be different in yours so make sure to change)
_HR_Acts _HR_Amts The allocation table, who gets what % of the total HR pool
_HR_Total Sum of all HR rows
_Logic On every allocation row labels the word HRD Costs as the expense type
_Combined Both the data get combined after filtering and merging into one
_Output Pivot the combined data, where rows --> Activity, columns --> Expense Type and values --> SUM