Problem with a sumproduct

[Sasikiran]

Dear,

I am struggling with a formula to count the number of mails received on a
particular day within a particular time slot. This data is copied from an
outlook and sorted according using a macro.

In sheet1 i have the raw data in column A to D as From, Subject, Date and
Time respectively. The date is in format 14-May and time in the format 15:00.
Now in the sheet2 in a cell I need a formula to calculate the number of mails
received on 20-Jun from 15:00 to 15:59 hours.

I am trying to use the below formula but is not identifying the date value
in column C and not giving the desired result.

=SUMPRODUCT((Raws!D25000>=--"15:00:00")*(Raws!D25000<=--"15:59:59")*(Raws!C2:C5000="20-Jun"))

Please help me in correcting the above formula or suggest me another one to
get the desired result.

Thanks in advance.

 

[Sean Timmons]

=COUNTIF(Raws!D25000>= DATEVALUE("6/20/2009 3:00 PM") -
COUNTIF(Raws!D25000>= DATEVALUE("6/20/2009 4:00 PM")

should be even easier

 

[T. Valko]

=SUMPRODUCT((Raws!D25000>=--"15:00:00")*(Raws!D25000<=--"15:59:59")*(Raws!C2:C5000="20-Jun"))

If the times are true Excel times and the dates are true Excel dates:

=SUMPRODUCT(--(HOUR(Raws!D25000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))

 

[Sasikiran]

Dear Valko,

The formula you've suggested isn't helping according to my need

=SUMPRODUCT(--(HOUR(Raws!D25000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))

 

[T. Valko]

Oh well!

--
Biff
Microsoft Excel MVP

Dear Valko,

The formula you've suggested isn't helping according to my need

=SUMPRODUCT(--(HOUR(Raws!D25000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))