countifs with a date criteria

drodgers
drodgers
edited 12/09/19 inFormulas and Functions

I am trying to create a countifs formula that would count "New Quotes" with a January date while it's referencing another sheet.

New Quotes would be in the New Quote/Repricing column of this spreadsheet and then there's the Proposal date column that is in a MM/DD/YY format.

=COUNTIFS({CPI Prospects 2019 Range 1}, "New Quote", {CPI Prospects 2019 Range 2}MONTH(@cell),01)

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
This will work for instances where there is a single first and single last name, separated by a space. If you get into more complicated names, things get harder to work out.<\/p>

=IF([Name]@row <> \"//www.santa-greenland.com/community/discussion/46971/\", LEFT([Name]@row, FIND(\" \", [Name]@row) - 1) + \".\" + RIGHT([Name]@row, LEN([Name]@row) - FIND(\" \", [Name]@row)) + \"@company.net\", \"//www.santa-greenland.com/community/discussion/46971/\")<\/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":109441,"type":"question","name":"SumIFS from another sheet with two conditions","excerpt":"I am trying to use the SUMIFS function to sum the total budget approved for one of the Functions when the STEP is only In-Flight. This is the formula I am using, but it's not working! =SUMIFS({Projects Function}, CONTAINS(\"Finance\"), {Projects Step}, CONTAINS(\"6 - In Flight\"), {Budget}, >25) I need the data from another…","snippet":"I am trying to use the SUMIFS function to sum the total budget approved for one of the Functions when the STEP is only In-Flight. This is the formula I am using, but it's not…","categoryID":322,"dateInserted":"2023-08-24T22:12:04+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T14:36:29+00:00","insertUserID":165562,"insertUser":{"userID":165562,"name":"nermine.elsaghir","title":"Senior Analyst, PMO","url":"https:\/\/community.smartsheet.com\/profile\/nermine.elsaghir","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T14:40:41+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"updateUserID":null,"lastUserID":165153,"lastUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T14:33:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":38,"score":null,"hot":3385891113,"url":"https:\/\/community.smartsheet.com\/discussion\/109441\/sumifs-from-another-sheet-with-two-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109441\/sumifs-from-another-sheet-with-two-conditions","format":"Rich","lastPost":{"discussionID":109441,"commentID":392585,"name":"Re: SumIFS from another sheet with two conditions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392585#Comment_392585","dateInserted":"2023-08-25T14:36:29+00:00","insertUserID":165153,"insertUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T14:33:43+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-25T13:32:17+00:00","dateAnswered":"2023-08-24T23:10:18+00:00","acceptedAnswers":[{"commentID":392505,"body":"

Hi @nermine.elsaghir<\/a> ,<\/p>

It looks like you are missing the range of what you want summed, which I assume is {Budget} - this should be the first argument in the formula. Additionally, is the values you are are using for your criteria are the entire values of the cell, you do not need to use \"CONTAINS\" (again, I am making an assumption that this is the case).<\/p>

Try this: =SUMIFS({Budget}, {Projects Function}, \"Finance\", {Projects Step}, \"6 - In Flight\", {Budget}, >25) <\/p>

Hope this helps,<\/p>

Dave<\/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":109392,"type":"question","name":"COUNTIF using OR","excerpt":"Hi, I'm trying to count how many check boxes (out of a possible 4) are checked on a row (basically, countif w OR x OR y OR z is checked). I've tried many variations of the following formula: =COUNTIFS(OR[PNS]@row = \"1\", [EIP]@row = \"1\")) Thank you!","snippet":"Hi, I'm trying to count how many check boxes (out of a possible 4) are checked on a row (basically, countif w OR x OR y OR z is checked). I've tried many variations of the…","categoryID":322,"dateInserted":"2023-08-24T13:49:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T17:10:27+00:00","insertUserID":141331,"insertUser":{"userID":141331,"name":"deb_63_hydracor","url":"https:\/\/community.smartsheet.com\/profile\/deb_63_hydracor","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzYn23TqUOGU8A4PW0HalCwzLK5YNQsVxHwYcgL=s96-c","dateLastActive":"2023-08-24T15:43:31+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-25T14:34:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":62,"score":null,"hot":3385786213,"url":"https:\/\/community.smartsheet.com\/discussion\/109392\/countif-using-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109392\/countif-using-or","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109392,"commentID":392393,"name":"Re: COUNTIF using OR","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392393#Comment_392393","dateInserted":"2023-08-24T17:10:27+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-25T14:34:17+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-24T15:57:53+00:00","dateAnswered":"2023-08-24T14:47:28+00:00","acceptedAnswers":[{"commentID":392343,"body":"

If your column title contains a space, it must be surrounded with square brackets. This should fix it.<\/p>

=COUNTIFS(PNS@row, 1) + COUNTIFS(EIP@row, 1) + COUNTIFS(Facilities@row, 1) + COUNTIFS([Patient Care Experience]@row, 1)<\/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