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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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())))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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())))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
So much easier. Thank you
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!