Consolidate Hourly data

Put this formula in H2:

=SUMPRODUCT((B$2:B$95>=F2)*(B$2:B$95<=G2))

then copy down to H5.

Hope this helps.

Pete

Thank you so much for quick help...

Apprciate....

RM

You're welcome - thanks for feeding back.

You might like to change the entry in F2 to 00:00:00, as you are 1 minute out in the example workbook.

Hope this helps,

Pete

Oh Thank U...
I have one more request...
Currently i have 6 hour span in the time range.
How would i change it to make it hourly..time range.
Or Lets say i want to create 3 hour time range, how would i do that.

Let me know if any questions.

Thank u so much.

RM

Hi bebo021999:

This works

I may need further help with the same attached excel sheet.
i will let you know...

Thanks a lot ,..

RM

I've put a span in cell E2 (set to 3 hours) and set up appropriate formulae in columns F G and H in the attached workbook. You can change the value in E2 (eg to 1:00:00), and copy the formulae in F9:H9 further down until you reach 23:59:59.

Hope this helps.

Pete

Hi Pete and bebo:

Thanks both of you to help accomplish...
I want to take this further as per atatched sheet.
Want to know for each State, number of entries in the time range.
Please refer to attached sheet.

Thanks and let me know if any questions.

RM

Well, you have to be more consistent with the data that you have put in column C - you spelt Los Angeles wrong; in one entry you have MBeach but no Florida; you have newjersey and newyork without spaces; and in other entries you have NY etc., etc. It will be very difficult trying to match up with these variations. However, if you did have consistent entries in column C, and in I1 to L1 you had those locations, then you could do this in I2:

=SUMPRODUCT(($B$2:$B$95>=$F2)*($B$2:$B$95<=$G2)*($C$2:$C$95=I$1))

This can be copied across and down to give your counts broken down by location as well as by time bands.

Hope this helps.

Pete

Sorry Guys:

I was travelling and now got back..
Thansk for the above solution, it works great...Love it

RM