Cross Sheet Formula, COUNTIFS + AND?

Hi!!!

Tried this and it is #UNPARSEABLE:

=COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31))

I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!

Tags:

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭
    Answer ✓

    Hi@MichelleR


    Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • Hey Michelle,

    It looks like you want the field to display the count if, a) cost savings = "1" and b) if the opened date is 1/1/2023, and c) the opened date is less than 12/31/2023

    You shouldn't need the "AND" in your equation, as "COUNTIFS" already assumes multiple requirements need to be met.

    Two ways you could easily do this using this equation:

    1. Define the dates you want to search between into your sheet as shown below. This way, you can reference these fields in your formula - just make sure to lock the reference to each with the dollar signs. In the snip below the "Opened" column is a Date column. I added the assumption that you want the cost savings count if the date is greater thanor equal to1/1/2023 or less thanor equal to12/31/2023. I created this equation in the sheet summary. (Shown in first snip) =COUNTIFS([Cost Savings]:[Cost Savings], 1, Opened:Opened, >=$Opened$1, Opened:Opened, <=$Opened$2)
    2. Alternatively, you could create aseparate sheetthat references your data sheet that has the cost savings and opened dates - with this you would not need to add the "Start Date 2023" and "End Date 2023"intoyour data sheet, and you could put the equation into a different column instead of the sheet summary. You would need to use the "Reference Another Sheet" button in this case to reference your data sheet. (Shown in the last 3 snips) =COUNTIFS({Test - Cost Savings}, 1, {Test - Opened}, >=[Start Date]@row, {Test - Opened}, <=[End Date]@row)
    image.png
    image.png
    image.png
    image.png


  • AravindGP
    AravindGP ✭✭✭✭
    Answer ✓

    Hi@MichelleR


    Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • @AravindGPThank you!! I overcomplicated the situation, the AND was totally unnecessary :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"

I figured it out! Updated formula to get the 2 decimal places as well.<\/p>

=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/107529/\") + \"% closed rate on ticket status for the month of \" + Month@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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"

You were, indeed, very close.<\/p>

=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/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":[]},{"discussionID":108832,"type":"question","name":"How do I create a IF(AND formula combining multiple cross references?","excerpt":"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…","snippet":"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…","categoryID":322,"dateInserted":"2023-08-10T23:46:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140084,"lastUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":49,"score":null,"hot":3383486357,"url":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","format":"Rich","tagIDs":[207,219,254,344,440],"lastPost":{"discussionID":108832,"commentID":390262,"name":"Re: How do I create a IF(AND formula combining multiple cross references?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390262#Comment_390262","dateInserted":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9UG82HSGCS9G\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:23:34+00:00","dateAnswered":"2023-08-11T00:00:03+00:00","acceptedAnswers":[{"commentID":390143,"body":"

I think I am following what you are attempting to do. Try this:<\/p>

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, \"AMEX\")<\/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":207,"urlcode":"Sales","name":"Sales"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"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