Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Stumped on a formula
So we have a dashboard and am trying to do a count for legal requests. Two metrics, one would be for all requests received and completed for the current month, and the second would be all requests received and completed for the year. Here's the two formulas but they don't seem to update correctly:
Current month: =COUNTIFS({Resolved Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
For the year: =COUNTIFS({Resolved Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
What am I doing wrong?
Answers
-
What error is it giving or how is the data incorrect. If incorrect data would need to see some examples.
-
It's supposed to be a live feed, but it is still showing the same calculated numbers, even though there are new requests each day that are completed.
-
The immediate issue is that the TODAY function only updates when the sheet is activated. This can be done by opening and saving the sheet daily, form submissions, update request submissions, or my personal preference:
Insert a date type column. Set up a Record A Date automation to run daily to populate this new date type column.
This will force update the TODAY function without you having to do anything with it after the automation is set up.
Additionally... Since you are specifying a year in your year calcs, that leads me to believe there could be multiple years within the source data. If this is the case, you are also going to want to account for the year in the Month calcs since there could be multiple years with the month of 6 in the sheet. The below will restrict it to only pull from the current month of the current year as opposed to the month of June for all years within the source data.
=COUNTIFS({Resolved Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
