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!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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.

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

should be even easier

"Sasikiran" wrote:

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!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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.

=SUMPRODUCT((Raws!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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!D2:D5000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))

--
Biff
Microsoft Excel MVP


"Sasikiran" wrote in message
...
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!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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.

Dear Valko,

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

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

"T. Valko" wrote:

=SUMPRODUCT((Raws!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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!D2:D5000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))

--
Biff
Microsoft Excel MVP


"Sasikiran" wrote in message
...
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!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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.

Oh well!

--
Biff
Microsoft Excel MVP


"Sasikiran" wrote in message
...
Dear Valko,

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

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

"T. Valko" wrote:

=SUMPRODUCT((Raws!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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!D2:D5000)=15),--(Raws!C2:C5000=DATE(2009,6,20)))

--
Biff
Microsoft Excel MVP


"Sasikiran" wrote in message
...
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!D2:D5000=--"15:00:00")*(Raws!D2:D5000<=--"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.