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

创建两个辅助列在你的源表

1. 月柱公式:=月(@r开放日期ow)
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