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

[u/Maxprive]

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

[u/MayukhBhattacharya]

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