Count instances in last week

[mushroomdk90]

I have a list in column A of dates for every event day (e.g. the 2., 3., 10., 13., and 14. of febuary 2013) and in column B i would very much like a formula to count the number of event days, but only for the last 7 days. In other words, i need to read out the pace over the last 7 days in terms of events/week. I cannot get COUNTIF to have a variabel cell as criteria. Can you help?

[mrice]

Try something like

=COUNTIFS(A:A,">"&NOW()-7,A:A,">"&NOW())

[bebo021999]

@mrice, as per your idea it may be shorten
=COUNTIF(A:A,">"&NOW()-7)

In my opinion, the last date may be not today:
=COUNTIF(A:A,">"&(MAX(A:A)-7))

[mushroomdk90]

Thanks alot! This formula seemed to work:

=COUNTIF(A$1:A1;">"&(MAX(A$1:A1)-7))/7

What does the syntax ">"&(MAX(A$1:A1)-7 mean?

[SDCh]

Try this:

=MAX(A$1:A10)

and this:

=MAX(A$1:A10)-7

you will know from the result


Regards,
SDCh