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

      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!