Consolidate Hourly data

[rizmomin]

Hello:

I have data

Column A : Date
Columb B : Time (h:mm:ss AM/PM)
Column C : Sub ID (Entries)


I need to know how many entries in time range as shown in attached excel sheet.

Please help with this easy solution.

Thanks for your kind help.

RM

[Pete_UK]

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

[rizmomin]

Thank you so much for quick help...

Apprciate....

RM

[Pete_UK]

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

[rizmomin]

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

[bebo021999]

That means 3 hour = 3/24 of day different between 2 ranges:

F3 = F2+3/24
Copy down

G2 = F2+3/24-1/1440
Copy down

[rizmomin]

Hi bebo021999:

This works

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

Thanks a lot ,..

RM

[Pete_UK]

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

[rizmomin]

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

[bebo021999]

Try this in I2:

Please Login or Register  to view this content.

Copy down and accross.

P/S : in C column, you may mistyped "Los_Angelas..." instead of Los_Angeles

[Pete_UK]

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

[rizmomin]

Sorry Guys:

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

RM