add another IF to an IF, AND, OR statement

    I have a worksheet which allows me to see the status of subscriptions exported from our CMS and to show data input errors made by colleagues. This has been working well until someone decided to add another class of subscriber called 'Trial'.


    I now need to show this on the error sheet which is currently calculated with the following:


    =IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),"",$J$5&I142))


    This checks that column A contains data (client reference number) then looks for 2 subscriber types (column I) and ignores them if they are present and not overdue (column L) or concatenates an error message in J5 with the subscriber type in column I and send the results to an error sheet.


    I have now been asked to show if they are a 'Trial' subscriber. Column L may not have a date in so I need to show all occurrences of 'Trial' (in column I) so if they are a Trial client, the cell returns TRIAL.


    I am sure it can't be that hard but inspiration has left me today! Any help gratefully received.


    Thanks
    Simon

    Re: add another IF to an IF, AND, OR statement


    Do you want it to be checked after or before ISBLANK(A142)? If so do you want it to be checked before or after I142 is checked for subscriber?


    If before ISBLANK(A142)

    Code
    =IF(I142="Trial","Trial",IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),"",$J$5&I142)))


    If before checking

    Code
    I142 [COLOR=#000000][FONT=Inconsolata]=IF(ISBLANK([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]A142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]),,IF(I142="Trial","Trial",[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF(AND([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]L142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-TODAY()>[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],OR([/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Subscriber RENEWAL"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Subscriber NEW"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])),[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$J$5[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])))


    [/FONT][/COLOR]If after I142

    Code
    =IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),IF(I142="Trial","Trial",$J$5&I142))


    Hope this helps.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login