Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
I need to count the number of occurences between the hours of the day. For
example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
Possibly:
=SUMPRODUCT(--(TEXT(A2:A1000,"hh\:\0\0")="10:00")) for times of arrivals from 10am to 10:59am. Modify range to suit. Hope this helps! Richard Beginner wrote: I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
Assuming the cells are formatted as time, keep in mind that the hours of the
day are counted as 1/24th each. So, 10:00 AM is 10/24ths, or 5/8ths of 1. Decimal version is .125. 11:00 AM is 11/24th. decimal is .4458333. So, your formula can be: =countif(B:B,.125)-countif(B:B.44583333) This assumes the start time is in column B. I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
=SUMPRODUCT((B3:B6<(11/24))-(B3:B6<(10/24)))
"Beginner" wrote: I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
Forgot to add =. See below for corrected.
"Sean Timmons" wrote: Assuming the cells are formatted as time, keep in mind that the hours of the day are counted as 1/24th each. So, 10:00 AM is 10/24ths, or 5/8ths of 1. Decimal version is .125. 11:00 AM is 11/24th. decimal is .4458333. So, your formula can be: =countif(B:B,=.125)-countif(B:B.44583333) This assumes the start time is in column B. I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
Hi
say the layout is: A_Time 10 - 11:00am 2 Member Id Arrive time Leave Time Time Stayed 1 10:30 11:00 00:30 2 11:46 12:15 00:29 3 10:49 12:00 01:11 4 11:35 12:45 01:10 the Arrive times are in column B from row 4 to 400 In B2 copy or type: =COUNTIF(B4:B400,"=10:00")-COUNTIF($B$4:B400,"=11:00") you will need to format the result as general. To show a cumulative total in say column E copy this into E4 =IF(B411/24,"",COUNTIF($B$4:B4,"=10:00")-COUNTIF($B$4:B4,"=11:00")) Time stayed in d4 is just b4-c4 Copy all the formulas down Regards Peter "Beginner" wrote: I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
Thanks to everyone for your assistance and quick response. I could use all of
these samples for my formula problem. Hope you have a Happy New Year!! "Billy Liddel" wrote: Hi say the layout is: A_Time 10 - 11:00am 2 Member Id Arrive time Leave Time Time Stayed 1 10:30 11:00 00:30 2 11:46 12:15 00:29 3 10:49 12:00 01:11 4 11:35 12:45 01:10 the Arrive times are in column B from row 4 to 400 In B2 copy or type: =COUNTIF(B4:B400,"=10:00")-COUNTIF($B$4:B400,"=11:00") you will need to format the result as general. To show a cumulative total in say column E copy this into E4 =IF(B411/24,"",COUNTIF($B$4:B4,"=10:00")-COUNTIF($B$4:B4,"=11:00")) Time stayed in d4 is just b4-c4 Copy all the formulas down Regards Peter "Beginner" wrote: I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count formula help!!
One more...
=sumproduct(--(hour(b1:b10)=10)) Beginner wrote: I need to count the number of occurences between the hours of the day. For example: Time arrived #of Members Member1 - 10:30 7:00 Formula Member2 - 11:46 10:00 Formula inserted Member3 - 10:49 11:00 and so forth Member4 - 11:35 How can I write a formula to count the number of members that arrive at 10:00 -11:00? Any or all help is appreciated!!! Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the $ in a count formula | Excel Discussion (Misc queries) | |||
Creating a Count formula | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
More Help Needed with Count formula | Excel Worksheet Functions | |||
Count numbers formed from another formula | Excel Discussion (Misc queries) |