Countifs statements for date fields that are in the past

Derrick P

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 😀

Paul Newcome

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())

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.

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

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.

Derrick

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