setting date parameters in cross sheet formulas

dave.mcpherson56751dave.mcpherson56751 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
10/06/18 Edited 12/09/19

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

Tags:

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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

     

Sign In or Register to comment.