COUNTIFS between dates & criteria.
Greetings Smartsheet Community,
I am attempting to cross reference another sheet and Count all theCompletedtasks within a certaintime frame.
So far I am successful counting the dates in the range using the following formula:
=COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1))
Next I'd like to add "AND" and "OR" ( to count all tasks "Completed") but I keep running into errors.
Does anyone have a solution?
Any advice would be greatly appreciated.
Thank you!
Josh
Comments
-
Thanks Luke, I figured it out.
Here's What I did:
=COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, "Completed")
For Completed
and
=COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, <>"Completed")
For Not Completed
-
Kelly Gabel ✭✭✭✭✭✭
Trying to use a similar formula and coming back with the wrong count. Can someone help me understand what's wrong?
=COUNTIFS({Estimated Mech Completion Date 1}, <=DATE(20, 10, 1), {Estimated Mech Completion Date 1}, >=DATE(20, 12, 31))
That's the exact formula I'm using. I'm trying to get a count of all rows in this range where the date is between 10/1/2020 and 12/31/2020. My "year" portion of the formula isn't 4 digits because my source sheet isn't using the 4 digit year...but I have tried it both ways with no luck.
@Paul Newcomejust tagging you because you rock with formulas!
Thanks!
-
Paul Newcome ✭✭✭✭✭✭@Kelly GabelIf your dates are in a date type column then you are going to still want to use the 4 digit year in your DATE functions. If they are not in a date type column, we would need to do some things a little differently.
-
Kelly Gabel ✭✭✭✭✭✭
@Paul NewcomeThey are in a date type column, but using the 4 digit year still didn't work in that formula...
-
Paul Newcome ✭✭✭✭✭✭
Sorry. I completely missed this part...
Switch your dates around. Your formula is currently saying to count dates that are less than 10/1 and greater than 12/31.
You want greater than 10/1 and less than 12/31.
-
Kelly Gabel ✭✭✭✭✭✭
Thanks@Paul NewcomeThat fixed it!
-
Paul Newcome ✭✭✭✭✭✭
-
Hi Paul,
I would love your help with a formula if you don't mind. You helped me the other day with a similar formula and I was so thankful!
I am trying to to do a count ifs formula to know the count between two dates and another criteria.
For example, I have a Company column and I want to know how many times "Contractor" is listed but only when the date is between Jan 1 and March 31st. So dates greater than 2020/1/1 and 2020/3/31.
Can you please help me? Thank you so much, and let me know if I need to provide greater detail.
-
Paul Newcome ✭✭✭✭✭✭
@Megan HarryYou could use something like this...
=COUNTIFS(Company:Company, "Contractor", Date:Date, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 03, 31)))
-
@Paul NewcomeYay thank you so much Paul! I was so close with the formula I was trying, but you helped me get it! I am very appreciative! It worked perfectly!
-
Paul Newcome ✭✭✭✭✭✭
-
Hi Paul,
I'm attempting to use your formula referenced on 10/16/20 but am referring to 2 separate data ranges within that formula and am receiving an "Invaid Data Type" error:
=COUNTIFS({RANGE 1}, [Category1]@row, AND({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))))
I have found the following formula to be successful:
=COUNTIFS({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))) but I wish to add references to those 2 separate data ranges.
Can you please provide any insight into why I receive an error when adding references to data ranges?
Thank you!
-
Paul Newcome ✭✭✭✭✭✭
@Michelle BestercyYou do not need the first AND statement.
=COUNTIFS({RANGE 1}, [Category1]@row, {RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26)))
-
Thank you so much, Paul! That solution worked for me.
Help Article Resources
Categories
Check out theFormula Handbook template!
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":"
