Problem with a sumproduct
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.
|