setting date parameters in cross sheet formulas
Hi,
I am looking to pull rolling data onto a dashboard. i have the cross sheet formula working well however i am looking for some help with an addition to this.
I would like to add to the formula to look at data for the past 30 days for one column and the past 60 days for another column. i am hoping this is pretty straight forward for someone with the knowledge, which i don't appear to have at the moment!!
current formula is : =COUNTIFS({We work feedback category October}, "Additional work request")
(We work feedback category October) is range1 and "Additional work request" is the Critereon1
i suppose my question is can i add another range to count backwards 30 days from today? and where would i add it in the formula.
Any help greatly appreciated as i have been trying this for over a week! #callmedumb!!
Thank you
Dave
Comments
-
Based on your description, you'll likely want to end up with something like this
=COUNTIFS({We work feedback category October}, "Additional work request",
@{first date range}, AND(@cell > TODAY(-30), @cell <= TODAY()))
+ COUNTIFS({We work feedback category October}, "Additional work request",
@{second date range}, AND(@cell > TODAY(-60), @cell <= TODAY()))
unless this is not an OR condition (count if BOTH past 30 days of column 1 and past 60 days of column 2)
Be aware the ranges need to be same size.
I hope that helps.
Craig
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!