count number of occurences per date per 30 minute block in excel 2003

[Marlaine]

Trying to extract by specific dates and for 30 minute blocks of time, the number of entries for each date, 30 minute block.

The data has the following fields
Date, Type of Call, Carrier, Time of Call.
I've calculated in separate fields the day (not relevant to this calc), the nearest hour "=ROUND(I2*(1440/60),0)/(1440/60)" and the nearest 30 minute block "=ROUND(I2*(1440/30),0)/(1440/30)"

I've tried setting up worksheets with each date and each 30 minute block shown and tried:
=SUM(IF(FREQUENCY(MATCH(Data!A:A,A28,0),MATCH(Data!K:K,B28,0))>0,1)). Cant get this to work
=SUM(IF(Data!A:A=A26,IF(Data!K:K>B26,IF(Data!K:K<C26,1,0)),0)). Get cell currently being evaluated gets a constant. Checked formatting all fine

So where these may all work at some point, it really is still a mass of hard to read data. Ive done pivot tables too but still a lot of data to look at.

Is there a way to summarize to a separate worksheet those dates, 30 minute value combinations that have multiple entries. Then I could do a chart based on that summarized data. The purpose of this data is to monitor how many calls we are getting for 30 minute time blocks then we know how many trucks we may need running. If I can get this raw data then I can look for trends whether weekends or Mondays etc are busier

Data Sample
Date M/D/Y Carrier Month (Calculated) Day Time of Day Nearest Hour (calculated) 30Min (Calculated)
3/18/2013 College 3 Mon 19:00 19:00 19:00
3/19/2013 ESSO 3 Tue 13:40 14:00 13:30
3/19/2013 Shell 3 Tue 00:15 0:00 0:30
3/19/2013 Shell 3 Tue 00:20 0:00 0:30

Any help appreciated. I have hit my expertise level. Sadly I have to stay with 2003. Another user of this data is Excel 2007 so when they use the data they will save as 2003 compatible

Thanks
Marlaine

[Marlaine]

Yeah! Love sumproduct. Finally figured it out

=SUMPRODUCT((Data!$A$2:$A$7500='June 30 Min'!U$3)*(Data!$K$2:$K$7500='June 30 Min'!$A15))

[FDibbins]

Nice 1!!! its always rewarding when you are able to answer your own questions klike that

Thanks for sharing the answer, too

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol (see my notes below)