Help pulling data from certain date range
I am currently pulling data from a mainsheet into a dashboard via a metrics sheet. Its working fine but I have been asked to start only showing data from the last 30 days on the dashboard. I am having problems figuring out how to add a date formula to my current formulas. An example of my current formula is =COUNTIF({Project Tactic}, CONTAINS("Ad - Digital", @cell)). I would now like to add an additional portion that would pull from the same sheet via the created by date (or possibly a Due date column) that would show what was created in the last 30 days (or future 30 if go by due date). Can anyone help with the additional portion of the formula? Thank you.
Best Answer
-
For the past 30 days formula, you added square brackets around the TODAY function. Removing those should take care of that.
For the second formula, I have had this issue before when working with dates in COUNTIFS functions before and just had to switch around which order we were putting things in.
=COUNTIFS({Due Date}, AND(@cell <= TODAY(30), @cell >= TODAY()), {Project Tactic}, CONTAINS("Ad - Digital", @cell))
Answers
-
You would first need to switch from a COUNTIF to a COUNTIFS with the S on the end to allow for multiple range/criteria sets. Then you would follow the same pattern to select your date range then specify the criteria.
Created in the past 30 days:
=COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Created Date Range}, >= TODAY(-30))
Due in the next 30 days:
=COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Due Date Range}, AND(@cell <= TODAY(30), @cell >= TODAY()))
-
Thank you but they don't seem to work.
Counted past 30 days, I changed the column to correct name: =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Created}, [>=TODAY(-30)]) - it comes back with #unparseable
Next 30 days, also have issue: =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Due Date}, AND(@cell <= TODAY(30), @cell >= TODAY())) comes back with #incorrect argument set
I am not sure what I am doing wrong.
-
For the past 30 days formula, you added square brackets around the TODAY function. Removing those should take care of that.
For the second formula, I have had this issue before when working with dates in COUNTIFS functions before and just had to switch around which order we were putting things in.
=COUNTIFS({Due Date}, AND(@cell <= TODAY(30), @cell >= TODAY()), {Project Tactic}, CONTAINS("Ad - Digital", @cell))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 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!