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

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

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

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

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

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

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

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