Sumproduct and Time Question

Hi all,

I'm setting up a worksheet to summarize the detail data. The sumary data will be in one worksheet and the detail data in another. I'm trying to calculate the average percent for different time periods (shifts) of the day. I'm having trouble adding the time to my sumproduct forumla.

My data looks like this. Five columns of data, one row of data for every five minutes of a day. I calculate the average of column E for each day fine. Now I want to calculate the average of column E for 7:00 AM to 7:00 PM of a given day.

Row column letters
no (A) (B) (C) (D) (E)
1 Auto dngitr30 In 3/1/08 12:03 AM 0.20
2 Auto dngitr30 In 3/1/08 12:08 AM 0.49
3 Auto dngitr30 In 3/1/08 12:13 AM 0.15
4 Auto dngitr30 In 3/1/08 12:18 AM 0.08
5 Auto dngitr30 In 3/1/08 12:23 AM 0.23
6 Auto dngitr30 In 3/1/08 12:28 AM 0.22
7 Auto dngitr30 In 3/1/08 12:33 AM 0.35
8 Auto dngitr30 In 3/1/08 12:38 AM 0.38
9 Auto dngitr30 In 3/1/08 12:43 AM 0.24
10 Auto dngitr30 In 3/1/08 12:48 AM 0.49
11 Auto dngitr30 In 3/1/08 12:53 AM 0.31
12 Auto dngitr30 In 3/1/08 12:58 AM 0.19
13 Auto dngitr30 In 3/1/08 1:03 AM 0.83
14 Auto dngitr30 In 3/1/08 1:08 AM 0.34

Here is the sumproduct formula I'm using for the entire day. I'm also curious, if there is an easier way to do this. The sumproduct formula is getting pretty big.

=SUMPRODUCT(--(Data!$A$1:$A$15000="Auto"),--(Data!$B$1:$B$15000="dngitr30"),--(Data!$C$1:$C$15000="In"),--(YEAR(Data!$D$1:$D$15000)=YEAR($A4)),--(MONTH(Data!$D$1:$D$15000)=MONTH($A4)),--(DAY(Data!$D$1:$D$15000)=DAY($A4)),(Data!$E$1:$E$15000))/SUMPRODUCT(--(Data!$A$1:$A$15000="Auto"),--(Data!$B$1:$B$15000="dngitr30"),--(Data!$C$1:$C$15000="In"),--(YEAR(Data!$D$1:$D$15000)=YEAR($A4)),--(MONTH(Data!$D$1:$D$15000)=MONTH($A4)),--(DAY(Data!$D$1:$D$15000)=DAY($A4)))

Thanks for any help

Assuming A4 contains just the date (no time), I'd use an AVERAGE "array formula", which is a little shorter.....

=AVERAGE(IF((Data!$A$1:$A$15000="Auto")*( Data!$B$1:$B$15000="dngitr30")*( Data!$C$1:$C$15000="In")*( Data!$D$1:$D$15000>=$A4+"07:00")*( Data!$D$1:$D$15000<=$A4+"19:00"), Data!$E$1:$E$15000))

confirmed with CTRL+SHIFT+ENTER

Thanks daddylonglegs and nbvc. The average formula worked great. I was able to change my other sumproduct functions to use the Average function also.

One other question! On another set of cells I calculate the average for the entire day. I used the same average formula, removed the time portion, and replaced it with YEAR, MONTH, and DAY. Is their a shorter way to test the date if the data worksheet olumn contains date and time and the summary worksheet column contains the date only.

=AVERAGE(IF((Data!$A$1:$A$65000="Auto")*( Data!$B$1:$B$65000="dngitr30")*( Data!$C$1:$C$65000="In")*(YEAR(Data!$D$1:$D$65000)=YEAR($A4))*(MONTH(Data!$D$1:$D$65000)=MONTH($A4))*(DAY(Data!$D$1:$D$65000)=DAY($A4)),Data!$E$1:$E$65000))