Countif after certain date

JAA
JAA
edited 12/09/19 inFormulas and Functions

Hi!

I'm trying to create a formula that will count the number of rows that have a "Delivery Date" after 1/1/2019. Any row with a date prior to that should not be counted. I also do not want blank rows counted.

In the screen capture attached, you'll see all but one populated row should be counted.

Any suggestions on a formula I could use? I appreciate any assistance you can provide!

Josh

2019-03-29_11-30-11.jpg

Tags:

Comments

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭
    edited 03/29/19

    I don't know how to input hard dates within SmartSheet formulas, so I have a solution by adding a new column. Call it whatever (I used "Anchor Date" to test it out), and copy/paste your 1/1/19 date into the entire row) then use:

    =COUNTIFS([Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 <> "", [Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 > [Anchor Date]1)

    You can drop this formula through the whole column if you'd like. And every row would show the total. If you didn't want the blank rows to show any number edit the formula to exclude blanks:

    =IF([Delivery Date]1 = "", "", COUNTIFS([Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 <> "", [Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 > [Anchor Date]1))

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

  • L_123
    L_123 ✭✭✭✭✭✭

    to input a hard date you use the Date Reference

    =Date(2019,12,31)

    will return december 31, 2019

    This can be used inside of formulas as well

  • JAA
    JAA
    edited 03/29/19

    Great! I accomplished what I needed with the below formula.

    =COUNTIFS([Delivery Date]:[Delivery Date], [Delivery Date]1 <> "", [Delivery Date]:[Delivery Date], [Delivery Date]1 > DATE(2019, 1, 1))

    2019-03-29_12-33-19.jpg

  • Thank you! This in addition to the above comment got me closer to where I'd like to be.

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭

    I will have to remember that one, it's not often I need to use it, but for those couple of times!

    Jason Tarpinian -Sevan Technology

    Smartsheet Platinum Partner

  • If I wanted to add in that the date would also have to be before today's date (so Jan 1st through today), what would you suggest?

  • L_123
    L_123 ✭✭✭✭✭✭

    if you want it to always be the current year and not have to update the formula annually you can use this

    date(year(today()),1,1)

  • Found what I needed:

    =COUNTIFS([Delivery Date]:[Delivery Date], [Delivery Date]1 <> "", [Delivery Date]:[Delivery Date], [Delivery Date]1 > DATE(2019, 1, 1), [Delivery Date]:[Delivery Date], [Delivery Date]1 < TODAY())

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/01/19

    You can also use the AND function for your criteria along with@cellreferences.

    =COUNTIFS([Delivery Date]:[Delivery Date], AND(ISDATE(@cell), YEAR(@cell= YEAR(TODAY()),@cell< TODAY()))

    You are only referencing one range. Your date range. You can use the AND function to combine all of the criteria to keep from having to repeat the range.

    In this particular formula we are saying to look at the [Delivery Date] column and count all cells that meet the following criteria:

    Is a date

    AND

    Has the current year

    AND

    is before today.

    .

    Here is a page that describes the various functions used in Smartsheet formulas.

    https://help.smartsheet.com/functions

    Here is a page that helps with troubleshooting errors with formulas and functions.

    https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.163823034.487922610.1554120052-1302373248.1552411124

    And if you look through the templates, there is one called "Smartsheet Formula Examples". This is an actual Smartsheet with all of the functions explained and shown in use along with a few hints and tips. Because it is a sheet, it is interactive. You can play around with the formulas to see what happens. If you mess something up, you can either use the Undo button or simply delete the sheet and re-download the template.

    thinkspi.com

  • Great info! I love the tip about the available template.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/p>

When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+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-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":51,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+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-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","snippet":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it…","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"

Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>

=IF([Month Column]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>

Replace [Month Column] with your column name.<\/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