Countifs statements for date fields that are in the past


Hi there,

I am looking to summarize data from a master sheet for inclusion into a dashboard via a project metrics sheet.

Within the master sheet I have an “Opening Date” that is populated when the opening date is decided.

I want to populate the Dashboard with the number of sites that HAVE opened each month (past tense).

Here is the formula that I have that works to summarize the openings by month (and year). The problem with this formula is that it also counts the sites that are forecasted to open.

=COUNTIFS({Opening Date}, IFERROR(MONTH(@cell), 0) = 8, {Opening Date}, IFERROR(YEAR(@cell), 0) = 2023)

Here is the formula that works for me to calculate the number of sites that have opened this year in the past.

=COUNTIF({Opening Date}, AND(@cell >= DATE(YEAR(TODAY()), 1, 1), @cell <= TODAY()))

I can not seem to figure out how to put the two together by month. 

Thank you for your assistance 😀

Best Answer


  • Ramzi
    Ramzi ✭✭✭✭✭

    Here's one way to do it. For the purposes of this example, I used the same sheet.

    Create two helper columns in your source sheet

    1. Month with column formula: =MONTH([Opening Date]@row)
    2. Year with column formula: =YEAR([Opening Date]@row)

    For your summary sheet (represented in shaded green in this example) list out the months in one column and then in the Count column use this column formula: =IF(AND([Month This Year]@row <> "", MONTH(TODAY()) > [Month This Year]@row, YEAR(TODAY()) = Year@row), COUNTIFS(Month:Month, [Month This Year]@row, Year:Year, YEAR(TODAY())))

    You will of course need to modify this to do cross sheet reference.

    Smartsheet Solutions Architect

  • Derrick P

    Hi Ramzi – Thanks!! Your help is very much appreciated.

    I have recreated your sheet and the formula works (of course😛) but when I try to enter into my sheet I get an #INCORRECT ARGUMENT SET error, so I have got something wrong with my cross referencing I am guessing.

    In my source sheet I have created the helper columns:

    “Helper – Opening Date Month”

    “Helper – Opening Date Year”

    And in my summary sheet I have added a column called

    “Month This Year”

    If I update your formula with the cross references, I get this formula.

    =IF(AND([Month This Year]@row <> "", MONTH(TODAY()) > [Month This Year]@row, YEAR(TODAY()) = {Helper – Opening Date Year}, COUNTIFS({Helper – Opening Date Month}, [Month This Year]@row, {Helper – Opening Date Year}, YEAR(TODAY()))))

    And it returns the #INCORRECT ARGUMENT SET error.

    When I look up the error in the Function Glossary, there is a function and an argument that is not lining up, but I can not see it. Are you able to?

    Again, I appreciate the assistance. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You don't need any helper columns. All you need to do is add a range/criteria set to your existing monthly COUNTIFS to include the date range being less than today.

    =COUNTIFS({Opening Date}, IFERROR(MONTH(@cell), 0) = 8, {Opening Date}, IFERROR(YEAR(@cell), 0) = 2023, {Opening Date}, @cell<= TODAY())

  • Derrick P

    Hi Paul,

    Thank you for this. I have been referencing your solutions since starting Smartsheet almost a year ago. I have a hard time wrapping my head around formulas with dates, and this was no different. Now it seems like an easy solution, but I just can't seem to get there myself. Thanks for doing this in this community, I hope it is still fun for you and you continue for a long while.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    It is definitely still fun for me even after almost 6 years of building solutions 40+ hours/week. I use it for my main job, the business I own, as well as many facets of my personal life and still look forward to logging in the next day, and there have been plenty of days where I won't even realize that it is already midnight. Hahaha

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!