0 Replies Latest reply: Aug 25, 2015 10:58 AM by Jeff Robertz RSS

    Product sum with multiple if

    Jeff Robertz

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



      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!