Product sum with multiple if

[agaetisproject]

Hi all,

As you can see on the doc attached, I am trying to calculate an amount for different profiles using their hourly rate.

The formula is in essence:

- if Cat E is personnel (1.* or 2.*) , then vlookup for the corresponding profile (Cat) and apply corresponding rate

- if Cat E is expense (3. and above), use the amount provided (there is no hours here)

If you look at the page 'Step 2 - input' I manage to get the formula working properly:

if([Cat. Expenditure]='1. Personnel (cat. PSS form 1)',sum(MonthlyFigure*[Hourly rate]),

if([Cat. Expenditure]='2. Direct Overhead  (cat. PSS form 9)',sum(MonthlyFigure*[Hourly rate]),

sum(MonthlyFigure)))

But on the sheet 'monthly distribution', when I try to replicate in a KPI box, it does not work.

It seems the aggregation does not happen without me understanding why.

Could you help me understand why there is a different behavior and help me fix this?

Many thanks in advance!