Forum Discussion
Count Dates in Range Based on Criteria
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
3 Replies
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))
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
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)
