Counting Dates within a Date column
Hi all,
I'm trying to set up a formula that counts specific dates within a date column, using the following approach:
=COUNTIF({Reference 1}, "31-12-2020")
It keeps returning 0 when there are least 4 dates within the target column that match the specified criteria.
Can anyone advise how I can fix this?
Many thanks in advance!
Best Answers
-
Bassam Khalil ✭✭✭✭✭✭
Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Debbie Sawyer ✭✭✭✭✭✭This works for me...
Are you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D
The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)
=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))
Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.
Pop a screen shot up on it in place if you are still getting unparseable.
Good luck
Debbie
Answers
-
Bassam Khalil ✭✭✭✭✭✭
Hope you are fine, your problem is when you define the criteria in count formula as a text so the result for sure will be 0, to solve this problem create help column for the criteria and input in that column the date you need to count then the formula will work correctly, i create for you a sample please check the following screen shot:
1- Reference column & Date Column are Date type.
2- Count column is Text/Number type
3- the formula in count column =COUNTIFS([Date Column]:[Date Column], Reference@row)
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I'm very well thank you, I hope you are too. Thank you for replying to me and for your clarification. Unfortunately, due to the structural requirements of the source data, I cannot input helper columns.
Is there any other way I can work around this? I tried running a report isolating the dates in question, so that I could run a simple =COUNT formula, but it seems Smartsheet doesn't consider Reports to be referenceable.
Many thanks,
Gerhard
-
Bassam Khalil ✭✭✭✭✭✭
Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I had thought of this option, but I was hoping to avoid duplication of data and use of multiple data sources.
Regardless, thank you again for your time and help, it is much appreciated!
Kind regards,
Gerhard Costa Pinto
-
Bassam Khalil ✭✭✭✭✭✭
You are welcome
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Andrée Starå ✭✭✭✭✭✭To add to Bassam's excellent advice/answer.
You could add a helper date field in the Sheet Summary section and reference that in the formula instead.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Debbie Sawyer ✭✭✭✭✭✭
Hi
I haven't visited the community for a while and I appreciate you have an accepted answer on this - I just wanted to add that you can achieve this without helper columns if you wanted to. I have created a little example for you. It works for me.
Hope this helps.
Kind regards
Debbie
-
Thank you so much for getting back to me and for your suggestion. I've just tried your suggestion and got an #UNPARSEABLE error.
Have I missed something? Please note, that I am referencing another sheet, butREFERENCE 1in the formula below is the date column in question.
=COUNTIF({REFERENCE 1},AND((DAY(@cell)=31,Month(@cell)=12,Year(@cell)=2020))
Kind regards,
Gerhard
-
Debbie Sawyer ✭✭✭✭✭✭
I believe you may have an extra ( in your formula...Try this
=COUNTIF({REFERENCE 1},(月日(@cell) = 31日(@cell) = 12年(@cell) = 2020))
-
Debbie Sawyer ✭✭✭✭✭✭
Just realised, if your cross sheet reference is referring to a column where there might be unfilled cells (Blank cells) then you might get an Invalid data type error return.
This formula fixes that:
=COUNTIFS({REFERENCE 1}, ISDATE(@cell), {REFERENCE 1}, AND(DAY(@cell) = 15, MONTH(@cell) = 12, YEAR(@cell) = 2020))
Hope this helps!
Kind regards
Debbie
-
Hi Debbie,
Thank you again for getting back to me! You were right in that the column being referenced has blank cells in it, however the proposed formula to solve this comes back as #UNPARSEABLE.
I'm fairly certain I got everything exactly as your formula, so I'm not sure why this isn't working.
=COUNTIFS({Portfolio Plan Range 3},ISDATE(@cell),{Portfolio Plan Range 3},AND(DAY(@cell)=31,MONTH(@cell)=12,Year(@cell)2020))
Any ideas?
Kind regards,
Gerhard
-
Debbie Sawyer ✭✭✭✭✭✭
This works for me...
Are you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D
The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)
=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))
Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.
Pop a screen shot up on it in place if you are still getting unparseable.
Good luck
Debbie
-
Debbie, not exactly sure what changed but this did it! Thank you so much!!
-
Debbie Sawyer ✭✭✭✭✭✭
Yay!
The only thing I could see was that Year was mixed case and not caps - but I can't imagine that was the error!
Glad it is working for you.
Kind regards
Debbie
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-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"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-26T01:04:51+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":23,"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":"
