CountIf with Date range



  • 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.

  • 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.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tom Fletcher It 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 ✭✭✭✭✭✭

    @Ess It 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

  • 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 Newcome Thanks so much! It worked.

  • 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?

  • I am having a similar issue and could really use some 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Amy Shank To 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 the Formula Handbook template!