Count if date is last 7, 30, X days

Leah

I have a sheet that lists feature requests. The system automatically generates a date when the request was submitted. I have summary columns up top of how many features were requested Q1-Q4 but I would like to add in counts for the past 7 days and 30 days. I would like a formula so the counts automatically update without myself having to adjust constantly. I have not been able to figure this out.  The formula I used for the quarterly counts were like this:

=COUNTIF([Date Requested]:[Date Requested], AND(@cell >= DATE(2017, 7, 1), @cell < DATE(2017, 10, 1)))

Any help would be greatly appreciated. 

Robert S.

Hello,

 

The formula can be written similarly to the one you've already created, but using the TODAY() function to keep it up to date. This is what I came up with:

Last 7 Days

=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-7)))

 

Last 30 Days

=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-30)))

 

The TODAY() function would allow this to change every day that you look at this. The negative number in the TODAY() function's parentheses can be set to any amount of days.

 

Shaine Greenwood

Hi Leah,

You might consider using the TODAY() function in your formula, for example:

=COUNTIF([Date Requested]:[Date Requested], <TODAY(-30))

Instead of using COUNTIF(AND()), you can use COUNTIFS:

=COUNTIFS([Date Requested]:[Date Requested], <TODAY(-30), [Date Requested]:[Date Requested], >TODAY(-7))

More on these functions in our functions list: https://help.smartsheet.com/functions

nfatolitis

I had the same question.  Thanks.