setting date parameters in cross sheet formulas

dave.mcpherson56751

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

J. Craig Williams

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