Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Count of cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Count of cells
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. On Aug 28, 11:25*pm, Vinu wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Count of cells
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 "Pete_UK" wrote in message ... 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. On Aug 28, 11:25 pm, Vinu wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count all cells which have same data | Excel Discussion (Misc queries) | |||
How to count all cells which have same data | Excel Discussion (Misc queries) | |||
Count Only Empty Cells AFTER Cells with Data | Excel Worksheet Functions | |||
Count data in cells | Excel Worksheet Functions | |||
Count cells with data | New Users to Excel |