Date use with COUNTIFS formula (Non-Specific Date)
I have set up a summary grid pulling information from another Support calls grid so I can keep track of logged calls completed by members of my team. I am currently using the formula below to pull that information for one of the team and it works fine.
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred")
The query I have is because I want to improve this. I would like to apply this to record only those calls completed in the current month and a second data set for only the current year. What would be the best way to accomplish this? I've only seen things for specific date ranges used so far which wouldn't be suitable for me.
Best Answers
-
My apologies. I clicked on "Submit" before posting the year...
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)
-
If you want current month and year, then you can use...
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Answers
-
For a single month (I will use March 2021 for this example) you wuld want:
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))
-
My apologies. I clicked on "Submit" before posting the year...
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)
-
Thanks @Paul Newcome but if i'm reading this right, that references values as the current month or year. Is that right? So every time the month changed, I would have to go back in and change the number in the formula and same for the year.
There must be a way to make this an automated process. I have created a column that will host a cell showing today's year and another showing today's month. I would like to reference those.
I seem to have sorted this now via the formula below
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Support calls Range 4}, IFERROR(MONTH(@cell), 0) = [This Month]1)
The [This Month}1 refers to a column with my month date in as =MONTH(TODAY(0))
-
If you want current month and year, then you can use...
=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
-
So much easier. Thank you
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!