Formula help counting back 30 days

dave.mcpherson56751dave.mcpherson56751 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
04/30/19 Edited 12/09/19

Hi I am trying to create a formula that count all entries by a certain person within the last 30 days. This is a cross sheet formula and seems to work well.

Now comes the however!

I have the below formula which does the calculation, however if a date is in the future it still counts it.

=COUNTIFS({Monmouthshire Housing incident reporting Range 1}, >TODAY(-30), {Monmouthshire Housing incident reporting Range 2}, "Sue Kidd")

Is there any advice on how to tweak this formula so it only counts 30 days in the past and discards future dates.

Thank you as always for any help or guidance.

Dave

Popular Tags:

Comments

  • Nic LarsenNic Larsen ✭✭✭✭✭

    Try this: 

    =COUNTIFS({Monmouthshire Housing incident reporting Range 1}, <=TODAY(),({Monmouthshire Housing incident reporting Range 1}, >=TODAY(-30), {Monmouthshire Housing incident reporting Range 2}, "Sue Kidd")

  • dave.mcpherson56751dave.mcpherson56751 ✭✭✭✭✭

    Hi Nic,

    Thank you for taking the time to look at this.

    I have copied the formula but unfortunately get "#UNPARSEABLE"

    Do you have any advice please.

    Dave

  • Nic LarsenNic Larsen ✭✭✭✭✭

    Sorry try again. I think when I was updating yours I added an extra character: 

    =COUNTIFS({Monmouthshire Housing incident reporting Range 1}, <=TODAY(), {Monmouthshire Housing incident reporting Range 1}, >=TODAY(-30), {Monmouthshire Housing incident reporting Range 2}, "Sue Kidd")

  • dave.mcpherson56751dave.mcpherson56751 ✭✭✭✭✭

    Hi Nic,

     

    That works a treat, I cannot thank you enough.

    Dave

  • Nic LarsenNic Larsen ✭✭✭✭✭

    Perfect happy to help

Sign In or Register to comment.