using sumproduct with dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

frogman7 - 10 Aug 2007 18:56 GMT

I am trying to get counts by name for all the ticket open from now
until 15 days ago then 16 to 30 days ago etc.

Name date and time
Johnson    08/08/2007 00:37
Jones    09/08/2007 02:20
Smith    09/08/2007 22:17
Smith    07/08/2007 20:33
Doe    19/07/2007 22:52
Michaels    09/08/2007 15:44
Michaels    09/08/2007 15:51
Doe    09/08/2007 21:43
Doe    13/07/2007 19:39
Doe    19/07/2007 19:43
Doe    19/07/2007 22:22
Doe    13/07/2007 20:31
Doe    13/07/2007 19:51
Russell    09/08/2007 21:08
Doe    18/07/2007 18:28
Russell    04/08/2007 00:34
Doe    25/07/2007 20:12
Smith    06/08/2007 20:55
Doe    30/07/2007 22:31

I want to use the current date to find all the Doe tickets that are
between:
now to 15 days ago
16 to 30 days ago
31 to 60 days ago
61 to 90 days ago
90+ days ago

Reply to this Message

frogman7 - 10 Aug 2007 19:30 GMT

> I am trying to get counts by name for all the ticket open from now
> until 15 days ago then 16 to 30 days ago etc.
[quoted text clipped - 27 lines]
> 61 to 90 days ago
> 90+ days ago

This is what I have so far but it gives me a #VALUE error
=SUMPRODUCT(--(TEXT(B1:B99,"dd/mm/yyyy HH:mm")>=TEXT(NOW()-15,"dd/mm/
yyyy HH:mm")),--(A1:A99="Doe"))

Reply to this Message

Peo Sjoblom - 10 Aug 2007 19:59 GMT

1. You make it awfully hard to help you by hard coding UK formats in your
formula,

If indeed the values in B are genuine dates and times you can simply use

=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15))

Of course if the dates are text you will get TRUE for all dates for Doe
you could also make sure there are no dates in the future

=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15),--(B1:B99<=NOW()))

Signature

Regards,

Peo Sjoblom

>> I am trying to get counts by name for all the ticket open from now
>> until 15 days ago then 16 to 30 days ago etc.
[quoted text clipped - 31 lines]
> =SUMPRODUCT(--(TEXT(B1:B99,"dd/mm/yyyy HH:mm")>=TEXT(NOW()-15,"dd/mm/
> yyyy HH:mm")),--(A1:A99="Doe"))

Reply to this Message

frogman7 - 13 Aug 2007 19:53 GMT

> 1. You make it awfully hard to help you by hard coding UK formats in your
> formula,
[quoted text clipped - 50 lines]
>
> - Show quoted text -

thanks that worked great

Reply to this Message