nested Sum if



I need to get total of selected roles occurrence which can fall in three different categories. For example, among all the users , I need to count how many users have role of ADM, and then  if they had the same role 3 months ago also- If yes it will be counted in No change list ( Category 1) and if their role is changed, It will be counted into new role (category 2 ) and third is the new  users who have no history 3 months ago ( Category 3). Following is the screen shot 


Following is the data table 


Can some one please guide me how can I achieve it. I tried using Sum if function  when role was not changed and it gives me following error 








  • Hi 

    In Anaplan, Syntax for sum formula is 

    Soruce.lineitem[sum : Mapping] 

    for if else,

    if "condition returns boolean" then 1 else 0. instead of 1 and 0, you can input logics to return desired value.

    for nested sum if, please try to code in below way.

    If "boolean condition" then source.lineitem[sum : mapping] else if "another boolean condition" then on. - this is for sequential nested if 

    if "boolean condition" then if "another boolean condition" then 1 else 0 else 0.. - this is for multiple condition nested if

    It is better to avoid nested if logics in anaplan unless it is required to achieve the result.


  • Thanks for replying Manjunath, I was able to achieve the solution using COUNT function in place of Sum.