Help pulling data from certain date range

Sheryl Walder

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.

Paul Newcome

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))

Paul Newcome

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()))

Sheryl Walder

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.

Paul Newcome

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))