Data Count of cells

[Vinu]

Dear Tean,
please help me in solving in below example.

I want to count how many calls loggedd for the diffiernt date for specific
time in below given format
e.g 0:00hrs to 3hrs 3:00hrs to 6hrs 6to 9hrs 15hrs to 18:00hrs


7/6/2008 13:00
7/6/2008 11.10 AM
7/6/2008 11.30 AM
7/7/2008 23:15
7/7/2008 23:15
7/6/2008 20:15
7/5/2008 03:15
7/6/2008 23:20

 

[Pete_UK]

For 0:00hrs to 3hrs, use: =COUNTIF(B:B,"<"&3/24)

For your next range, 3:00hrs to 6hrs, use this:

=COUNTIF(B:B,"<"&6/24) - COUNTIF(B:B,"<"&3/24)

The next range, 6 to 9hrs, will be given by:

=COUNTIF(B:B,"<"&9/24) - COUNTIF(B:B,"<"&6/24)

Similarly, 9hrs to 15hrs will be obtained with:

=COUNTIF(B:B,"<"&15/24) - COUNTIF(B:B,"<"&9/24)

and so on.

Hope this helps.

 

[David Biddulph]

You may have missed the fact that he's got dates as well as times, Pete.
Need MOD(...,1) to get rid of the date part and be left with time.
If the OP is saying that a result is wanted for each time slot for each day,
then it looks like a SUMPRODUCT, with a conditioon on INT(...) for the date
part and on MOD(...,1) for the time part.
--
David Biddulph

For 0:00hrs to 3hrs, use: =COUNTIF(B:B,"<"&3/24)

For your next range, 3:00hrs to 6hrs, use this:

=COUNTIF(B:B,"<"&6/24) - COUNTIF(B:B,"<"&3/24)

The next range, 6 to 9hrs, will be given by:

=COUNTIF(B:B,"<"&9/24) - COUNTIF(B:B,"<"&6/24)

Similarly, 9hrs to 15hrs will be obtained with:

=COUNTIF(B:B,"<"&15/24) - COUNTIF(B:B,"<"&9/24)

and so on.

Hope this helps.