if(and formula

davidroy
davidroy
edited 12/09/19 inFormulas 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 ✭✭✭✭✭✭

    Are your ranges all single cells?

  • 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 ✭✭✭✭✭✭

    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) ^

  • 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))),指数({时间和母亲ial 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 ✭✭✭✭✭✭

    看看这个工作…

    =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))),指数({时间和母亲ial 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)

  • 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 ✭✭✭✭✭✭

    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.

  • 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))),指数({时间和母亲ial 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 ✭✭✭✭✭✭

    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 theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/51956/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/51956/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions