How do I count the number of events in a 90 day period?

E

ericball

I have a list of 6000 events and the date each occurred.
For each date, I need to count the number of events since 90 days prior.
How can I count backwards until I reach the first occurrence of a date that
is more than 90 days earlier (and then stop)?

Exit Date 90 Days Ago # Exits in Between
1-Jan-1978 3-Oct-1977
12-Apr-78 12-Jan-1978 0
12-May-78 11-Feb-1978 1
1-Jul-78 2-Apr-1978 2
1-Jul-78 2-Apr-1978 2
20-Jul-78 21-Apr-1978 3
2-Aug-78 4-May-1978 4
What formula can I use to calculate the last column?
Many thanks.
 
D

Don Guillett

try this idea for -90. Substitute today() for the date in a list
=SUMPRODUCT(($A$3:$A$23>TODAY()-90)*($A$3:$A$23<TODAY()))
 
T

T. Valko

I guess by your example the result is *exclusive* of the dates?

Try this:

=SUMPRODUCT(--(A$2:A$8>B2),--(A$2:A$8<A2))

Copy down as needed.

The results based on your sample are: 0,0,1,2,2,3,4

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top