Countif the Month/Year matches the current Month/Year?
Current formula displayed at bottom. It counts the amount of Values in Range 1 that are created in the last 30 days. The formula works, however I'd like to change the second countifs statement, in particular the Today(-30) so that it matches the month/year of the date. That way, it will track the Values in Range 1 created that's creation month matches the current month of the year. {Range 2} is a "Created" Date column. I also have 2 columns created, Month and Year, that extract the Month and Year from that "Created" column. I'm just not sure how to translate that into the countifs formula.
Current formula is =COUNTIFS({Range 1}, Values@row, {Range 2}, >TODAY(-30))
Best Answer
-
We can use the MONTH Function and YEAR Function along with the TODAY Function to see if these two criteria match!
Ex:
MONTH(@cell) = MONTH(TODAY())
However sometimes the first part will error if there are blank cells in the range or cells with text. To ignore these, we'll wrap that in an IFERROR:
IFERROR(MONTH(@cell), 0) = MONTH(TODAY())
Try this full formula:
=COUNTIFS({Range 1}, Values@row, {Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Range 2}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
We can use the MONTH Function and YEAR Function along with the TODAY Function to see if these two criteria match!
Ex:
MONTH(@cell) = MONTH(TODAY())
However sometimes the first part will error if there are blank cells in the range or cells with text. To ignore these, we'll wrap that in an IFERROR:
IFERROR(MONTH(@cell), 0) = MONTH(TODAY())
Try this full formula:
=COUNTIFS({Range 1}, Values@row, {Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Range 2}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That worked, thanks Genevieve!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!