if(and formula

Options
davidroy
davidroy
edited 12/09/19 in Formulas and Functions

I'm having trouble with a "if(and" formula.

=IF(AND({KCPL Hours_ASSIGNED TO} = [Assigned To]@row, {KCPL Hours_TASK} = [Task Name]@row, {KCPL Hours_Project Number} = [Task Name]6), "1", "2")

But I get #INVALID OPERATION error message.

Any help would greatly be appreciated.

Thanks,

David

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • davidroy
    Options

    No, Columns

    David

  • davidroy
    Options

    In running a test to see if it worked I change it to:

    =IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})), "1", "2")))

    Now get UNPARSEABLE

    David

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    This second formula looks a little better. Your first was was essentially saying "if the entire column equals x". This newer formula is pulling an individual cell to compare to an individual cell. There are just a couple of suggestions I have, and there is one issue that is causing your error.

     

    1. The issue causing the error is that you need to remove two of the closing parenthesis from the end of the formula and add one after the last INDEX/MATCH to close out your AND statement.

    NUMBER})), "1", "2")))

    should be

    NUMBER}))), "1", "2")

    .

    2. I suggest specifying 0 (zero) as the sorting type in your MATCH statement. While it may not be necessary, I personally have found that I get the most consistent and accurate results using it.

    .

    3. If you are planning on using the 1 and 2 as actual numbers later on, you would want to remove the quotes from around them. Using the quotes turns it into a text string that just looks like a number but can't be used as a number. Removing the quotes allows you to use it as a number in future calculations.

    (Just an observation here) ^

  • davidroy
    Options

    Great that worked. Thanks.

    Now I'm moving on to the next part.

    I'm using the following formula and it works with on exception.

    =IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})), [Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0)

    The above gives me the correct amount if the AND is met, but where there is a problem it shows a #NO MATCH if the AND fails.  That fine except I need for it to be a zero (0).

    I added an IFERROR

    =IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})),[Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row , {KCPL Hours_ASSIGNED TO}))), 

    INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0),0)

    but with the AND matching all I a zero instead of an amount.  I know I have something wrong, but since I'm new to this cannot see it.  Thanks for your help!

    David

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    See if this works...

     

    =IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})),[Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row , {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0))),0)

  • davidroy
    Options

    Not quite.  The cell I put it should fail the AND and show zero. 

    But it shows a dollar amount.

    Almost there.

    David

     

    PS: Do you require additional info?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Some screenshots would be really helpful.

     

    Lets break it down and do some trouble shooting.

     

    Take each portion of the AND statement and put it in it's own IF statement. Keep the return values simple. Something like "Works" for the if true portion and "Check" for the else section.

     

    Put in dummy data so that they should all be true.

  • davidroy
    Options

    I got it figured out (see formula below)

    =IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]5 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]5, {KCPL Hours _PROJECT_NUMBER})), [Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0), 0)

    Seems I had a couple extra ")" in the formula.

    Thanks again,

    David

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Glad you got it working. yes

     

    Those parenthesis can get a little messy.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!