How do I count the number of events in a 90 day period? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              ericball 2/11/2007 5:18 PM PST   Question   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.     Was this post helpful to you?         Reply |   Top           Don Guillett 2/11/2007 5:31 PM PST   Answer   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())) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ericball" <ericball@discussions.microsoft.com> wrote in message news:319D3718-E0F5-4275-A13C-2C85507FA0BE@microsoft.com... >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. > >     Was this post helpful to you?         Reply |   Top           T. Valko 2/11/2007 6:50 PM PST   Answer   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 "ericball" <ericball@discussions.microsoft.com> wrote in message news:319D3718-E0F5-4275-A13C-2C85507FA0BE@microsoft.com... >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. > >     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies