How to record monthly changes in sheet?
So I am trying to get a formula to return a count of the number of cells that match but only if another column that records a date is within the last 30 days. I tried with reports but the issue is they don't display right if I try to send updates in any way besides a link (and I need it to run monthly, weekly, etc.) I felt like maybe having the dashboard update monthly based on this would be the best way but I can't figure it out. So I have the project sheet, a secondary metric that pulls in all the rows from the sheet and totals count of certain statuses. I tried to pull it into another sheet and use the formula in that sheet to pull only the sites that ,match the date ranges, but no matter what I do the formula doesn't work. I based it off several other threads in the community but can't seem to get it right. This is the formula I've tried but says "unparseable" will edit out specific information by replacing with asterisks:
=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=(TODAY()), <=(TODAY([-30])
I also tried with the greater/less than the other way in case I was messing it up but it's not working.
Best Answers
-
@KC Jones You're almost there, just some syntax issues, and a reversed operator.
You can list the same range twice in a COUNTIFS if you need to, in order to forego the potential syntax issues of AND. I'll show you the proper syntax both ways though:
=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=TODAY(), {******* Project Schedule Range 2}, >= TODAY(-30))
With AND:
=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, AND(@cell <=TODAY(), @cell >= TODAY(-30)))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman It works, thanks!
Answers
-
Oh forgot to mention I tried with AND after the first criterion and still didn't get it.
-
@KC Jones You're almost there, just some syntax issues, and a reversed operator.
You can list the same range twice in a COUNTIFS if you need to, in order to forego the potential syntax issues of AND. I'll show you the proper syntax both ways though:
=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, <=TODAY(), {******* Project Schedule Range 2}, >= TODAY(-30))
With AND:
=COUNTIFS({******* Project Schedule Range 1}, "Potential", {******* Project Schedule Range 2}, AND(@cell <=TODAY(), @cell >= TODAY(-30)))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman It works, thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!