Count Dates in Range Based on Criteria

[thomasm516]

I have the below column of dates in a named range.

9/1/2020

9/8/2020

9/9/2020

9/10/2020

9/15/2020

9/20/2020

9/22/2020

9/28/2020

9/29/2020

10/1/2020

10/2/2020

10/8/2020

10/10/2020

10/12/2020

10/13/2020

I want to find how many dates in the list are within the last week, within the last two weeks, and within the last month.  I thought this would be easy.  Using dates within the last week as an example, I thought the formula would be:

=CountIf(Dates,">Today()-7")

While that logic might be correct, the formula does not work.  I haven't been able to find another function that works, and I haven't been able to make the CountIf() syntax work.

This spreadsheet will be used on a device that isn't allowed to run macros, so this needs to be done with formulas only.

Can anyone show me how I would do this?

Thanks in advance for any help that you can offer!

--Tom

[#2]

@thomasm516 

As an example for month
{=SUM(IF((A1:A15<>"")*(MONTH(A1:A15)=C1), 1/COUNTIF(A1:A15,A1:A15)))}
Array formula: complete the formula with CTRL + SHIFT + RETURN!

The example file is attached.

Hope I could help you in your project.

Nikolino
I know I don't know anything (Socrates)

[#3]

@thomasm516 

The syntax of the criterion field was not correct

The formula ">Today()-7" will not evaluate the way you want inside the quotes.  

Try

= COUNTIF(dates,">"&(TODAY()-7))

[#4]

@Peter Bartholomew 

Thanks for the reply!  That is exactly what I was trying to do.  When I looked at the CountIf help subject last night, I did not see that option.  But looking at it again today, I see that it is indeed in there.  The help gives this example:

=COUNTIF(B2:B5,"<>"&B4)

Should have seen that before!

Again, thanks for the help!

--Tom