CountIf with Date range

2456

Answers

  • Hi Paul,

    Yes, CD1 is created date and it was from the source sheet and the Select Week is my selector of the Week (in yellow). I made one cell with a drop-down so that they can select which week to display. See the screenshot below. I just want to align the cut-off per week to Saturday-Friday since the default of WEEKENUMBER function is Monday to Sunday.

    Capture1.PNG


  • Tom Fletcher
    edited 12/14/20

    Hi@Paul Newcome,

    I was wondering whether you could help me with the following - struggling to get this one to work. The first part of the formula works fine, however when I add in the "AND" date range criteria, it fails:

    =COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, AND([Reported Date]:[Reported Date] >= DATE(2020, 1, 1), [Reported Date]:[Reported Date] <= DATE(2020, 1, 31)))

    I'm basically trying to sum up the number of 'incident type' for a given location for a particular month.

    Any guidance you can provide would be hugely appreciated!

    Thank you.

    Tom

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tom FletcherIt is the syntax. With COUNTIFS it should be range, criteria, range, criteria. The AND statement is a logical statement which acts as the criteria portion. What you would need is

    range, AND(@cell > this, @cell < that)

    =COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date],AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 1, 31)))


    In this particular instance though it looks like you are counting for a full calendar year in which case you could use

    =COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date],YEAR(@cell) = 2020)

    and to avoid some of the more likely issues...

    =COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date],IFERROR(YEAR(@cell), 0) = 2020)

  • Ess
    Ess ✭✭✭

    @Paul Newcome

    Hi Paul, I am trying to get a formula to calculate "Not Started", "In Progress" and "Complete" tasks for specific dates.

    Lets say the project runs from July 1st 2020 to June 30th 2023.

    I need my tasks rollup pie chart to show me tasks from July 1st 2020 to January 31st 2021 and not future complete roll up of start of project to end of project.


    Please help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @EssIt would be along the same lines of the solutions provided above.


    =COUNTIFS(Status:Status, "Not Started", Dates:Dates, AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd)))

  • Ess
    Ess ✭✭✭

    @Paul Newcome

    I'm getting an error UNPARSEABLE

    Capture.PNG


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to leave "@cell" as is.


    I also didn't realize you were referencing two separate date columns. In that case you are going to want...

    =COUNTIFS(Status:Status, "Not Started", [Start Date]:[Start Date], @cell>= DATE(2020,07,01), [End Date]:[End Date], @cell<= DATE(2020,12,31))

  • Ess
    Ess ✭✭✭

    @Paul NewcomeThanks so much! It worked.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • Hello together, i found this post by searching for a Soloution for my Problem.


    Im trying to make a Formular that count Rows from another Sheet if they meet 2 criterias the first is to check if the Value from the same sheet where the formular stands is same and the second criteria is to check if the Date is in Range. this is my formular


    =COUNTIFS({Meldung Supportbedarf 2020/2021 Bereich 1}; [IT Assurance Leads]@row; AND({Lead Liste Bereich 1}@cell <= DATE(2021;01;01);@cell DATE (2021;12;31))


    but i get a unparable error. Someone got an idea?

  • 我有一个类似的问题,真的可以使用ome help. I need to count all of the contracts won & lost by month. Similarly I need to sum all of the contracted proposals budgets won/lost by month.

    Snip20210226_23.png
    Snip20210226_24.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Amy ShankTo get the count, it would look something like this...

    =COUNTIFS({Won Column}, 1, {Date Won Column}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

    Just change the 3 and the 2021 to the appropriate month and year you are wanting to count.


    To get the sum, it would look very similar.

    =SUMIFS({Budget Column}, {Won Column}, 1, {Date Won Column}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

  • Tabitha W.
    Tabitha W. ✭✭✭✭

    Hi all, I am trying to follow this thread but having some problems. I am referencing this formula on a rollup sheet. Here is my formula and it is returning #UNPARSEABLE:

    =COUNTIFS({On the Job Injury Log Range 5}, "Employee Returned To Work", AND{On the Job Injury Log Range 1}, IFERROR(YEAR({On the Job Injury Log Range 1}, 0) = 2020)))


    Any help would be appreciated- thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tabitha W.Try this...

    =COUNTIFS({On the Job Injury Log Range 5}, "Employee Returned To Work", {On the Job Injury Log Range 1}, IFERROR(YEAR(@cell, 0) = 2020))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

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":23,"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":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/45376/countif-with-date-range/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/45376/countif-with-date-range/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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