How do I create a IF(AND formula combining multiple cross references?

Krystal Garcia
Krystal Garcia ✭
inFormulas and Functions
image.png

In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula:

=COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback Intake Sheet Type of CB}, "AMEX")))

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I think I am following what you are attempting to do. Try this:

    COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, "AMEX")

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@RossG<\/a> <\/p>

This formula will check if the Invoiced Date isblank and leave the cell blank if true. IF false it will check the Month and Date formulas to be less then 2 characters long and add the leading 0 if needed.<\/p>

=IF(ISBLANK([Invoiced Date]@row), \"//www.santa-greenland.com/community/discussion/108832/\", YEAR([Invoiced Date]@row) + IF(LEN(MONTH([Invoiced Date]@row)) < 2, \"-0\", \"-\") + MONTH([Invoiced Date]@row) + IF(LEN(DAY([Invoiced Date]@row)) < 2, \"-0\", \"-\") + DAY([Invoiced Date]@row))<\/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":108909,"type":"question","name":"Calculate # of Incidents Within A Month","excerpt":"I have a sheet that is recording data, we have a field for the date of an incident. I have a Roll Up sheet that is counting the data from the main one, with a number of different items. Is there a way to have a formula that counts the # of rows that have a date in a specific month? I want to be able to pull the number of…","snippet":"I have a sheet that is recording data, we have a field for the date of an incident. I have a Roll Up sheet that is counting the data from the main one, with a number of different…","categoryID":322,"dateInserted":"2023-08-14T14:25:06+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T15:59:59+00:00","insertUserID":154851,"insertUser":{"userID":154851,"name":"Jodi C","url":"https:\/\/community.smartsheet.com\/profile\/Jodi%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5qlOufSjfDo!xBtOMYTHJno!KTQ5OLC8XX5","dateLastActive":"2023-08-14T16:01:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-14T21:17:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":55,"score":null,"hot":3384054905,"url":"https:\/\/community.smartsheet.com\/discussion\/108909\/calculate-of-incidents-within-a-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108909\/calculate-of-incidents-within-a-month","format":"Rich","lastPost":{"discussionID":108909,"commentID":390495,"name":"Re: Calculate # of Incidents Within A Month","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390495#Comment_390495","dateInserted":"2023-08-14T15:59:59+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-14T21:17:13+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-14T15:10:17+00:00","dateAnswered":"2023-08-14T14:44:35+00:00","acceptedAnswers":[{"commentID":390460,"body":"

You would use something along the lines of (Jan 2023):<\/p>

=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1<\/strong>, IFERROR(YEAR(@cell), 0) = 2023<\/strong>))<\/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":108911,"type":"question","name":"IF formula not working for a drop down list","excerpt":"Hello, I'm trying to equate a numerical value to a drop down list when I receive a form entry. Here is my list: 4 - very clear 3 - clear 2 - somewhat clear 1 - not clear The formula I entered was =IF([Training Objectives]@row = \"1 - not clear\", 1, IF([Training Objectives]@row = \"2 - somewhat clear\", 2, IF([Training…","snippet":"Hello, I'm trying to equate a numerical value to a drop down list when I receive a form entry. Here is my list: 4 - very clear 3 - clear 2 - somewhat clear 1 - not clear The…","categoryID":322,"dateInserted":"2023-08-14T14:32:56+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T15:02:24+00:00","insertUserID":143464,"insertUser":{"userID":143464,"name":"Courtney M","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZMErGR_kbWs!zDErcze5yPs!34pFnjKNrYv","dateLastActive":"2023-08-14T19:06:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T16:26:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":50,"score":null,"hot":3384050720,"url":"https:\/\/community.smartsheet.com\/discussion\/108911\/if-formula-not-working-for-a-drop-down-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108911\/if-formula-not-working-for-a-drop-down-list","format":"Rich","lastPost":{"discussionID":108911,"commentID":390469,"name":"Re: IF formula not working for a drop down list","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390469#Comment_390469","dateInserted":"2023-08-14T15:02:24+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T16:26:14+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-14T15:25:45+00:00","dateAnswered":"2023-08-14T14:38:51+00:00","acceptedAnswers":[{"commentID":390454,"body":"

Hi @Courtney M<\/a>,<\/p>

It's an easy fix - you have an extra comma and bracket at the end of your formula. It should be:<\/p>

=IF([Training Objectives]@row = \"1 - not clear\", 1, IF([Training Objectives]@row = \"2 - somewhat clear\", 2, IF([Training Objectives]@row = \"3 - clear\", 3, IF([Training Objectives]@row = \"4 - very clear\", 4))))<\/p>

Hope this helps!<\/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":[]}],"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

  • Changing MMDDYYYY date type column to YYYYMMDD format when referenced in another cell

    The formula I currently have works to an extent, however, it omits the leading 0 on months and days. For instance, I get 2023-8-14 instead of 2023-08-14. Is there any way to add the zero? Here's what I have right now: =IFERROR(YEAR([Invoiced Date]@row) + "-" + MONTH([Invoiced Date]@row) + "-" + DAY([Invoiced Date]@row) + "…

  • Calculate # of Incidents Within A Month

    I have a sheet that is recording data, we have a field for the date of an incident. I have a Roll Up sheet that is counting the data from the main one, with a number of different items. Is there a way to have a formula that counts the # of rows that have a date in a specific month? I want to be able to pull the number of…

  • IF formula not working for a drop down list

    Hello, I'm trying to equate a numerical value to a drop down list when I receive a form entry. Here is my list: 4 - very clear 3 - clear 2 - somewhat clear 1 - not clear The formula I entered was =IF([Training Objectives]@row = "1 - not clear", 1, IF([Training Objectives]@row = "2 - somewhat clear", 2, IF([Training…