We have a high number of patients that come in between 6 am and 6 pm and we
need to count the times that they sign in. i.e. Every patient that signs in
at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a total....etc.
Data is generated daily on a seperate sheet that I have to count so was
looking for a "portable formula" that I can paste and get the time
breakouts. Expected results 6=23 7=34 8=61 etc. (meaning there were 23
patients between 6 am and 6:59) TIA

A few assumptions:
You have a table somewhere with at least one columns which contains time
stamps (properly inputted as time, not text).

you then have a report table setup somewhere with 1st column(say, column A
on sheet1) containing hours, such as the following, let say starting in cell
a2:
6:00 am
7:00 am
.....
6:00 pm

Note that I'm assuming there are no time stamps AFTER or on 6 pm. If you
want inclusive, change time to 6:01 pm.

in B2, your formula is then:
=COUNTIF('Sheet1'!A:A,"="&A2)-COUNTIF('Sheet1'!A:A,"="&A3)

You can then copy this down, and it will give you counts correlating to
signing between the two boundaries.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meebers" wrote:

We have a high number of patients that come in between 6 am and 6 pm and we
need to count the times that they sign in. i.e. Every patient that signs in
at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a total....etc.
Data is generated daily on a seperate sheet that I have to count so was
looking for a "portable formula" that I can paste and get the time
breakouts. Expected results 6=23 7=34 8=61 etc. (meaning there were 23
patients between 6 am and 6:59) TIA

Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm and
we need to count the times that they sign in. i.e. Every patient that
signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a
total....etc. Data is generated daily on a seperate sheet that I have to
count so was looking for a "portable formula" that I can paste and get the
time breakouts. Expected results 6=23 7=34 8=61 etc. (meaning there
were 23 patients between 6 am and 6:59) TIA

Luke..here are my results. your assumptions are correct. I copied the time
column and pasted it starting in A2, pasted your formula in B2 the results
are like this:
6:45 1
6:49 0
6:49 2

Expected answer 6=3, even though I can add 1+0+2 to get 3, it is just as
easy as counting the interval by eye. Unless I missed something?? Thanks
for your comment...


"Luke M" wrote in message
...
A few assumptions:
You have a table somewhere with at least one columns which contains time
stamps (properly inputted as time, not text).

you then have a report table setup somewhere with 1st column(say, column A
on sheet1) containing hours, such as the following, let say starting in
cell
a2:
6:00 am
7:00 am
....
6:00 pm

Note that I'm assuming there are no time stamps AFTER or on 6 pm. If you
want inclusive, change time to 6:01 pm.

in B2, your formula is then:
=COUNTIF('Sheet1'!A:A,"="&A2)-COUNTIF('Sheet1'!A:A,"="&A3)

You can then copy this down, and it will give you counts correlating to
signing between the two boundaries.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meebers" wrote:

We have a high number of patients that come in between 6 am and 6 pm and
we
need to count the times that they sign in. i.e. Every patient that signs
in
at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a
total....etc.
Data is generated daily on a seperate sheet that I have to count so was
looking for a "portable formula" that I can paste and get the time
breakouts. Expected results 6=23 7=34 8=61 etc. (meaning there were
23
patients between 6 am and 6:59) TIA

Biff, with times in A2:A30 I pasted in your formula and got the #VALUE. IF
this only counts 6-6:59 I would have to paste in 12 different formulas?? by
dragging it horizontal and changing the last number to 7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm and
we need to count the times that they sign in. i.e. Every patient that
signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a
total....etc. Data is generated daily on a seperate sheet that I have to
count so was looking for a "portable formula" that I can paste and get
the time breakouts. Expected results 6=23 7=34 8=61 etc. (meaning
there were 23 patients between 6 am and 6:59) TIA

If you got a #VALUE! error then your times aren't true Excel times, or, you
may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE. IF
this only counts 6-6:59 I would have to paste in 12 different formulas??
by dragging it horizontal and changing the last number to 7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm and
we need to count the times that they sign in. i.e. Every patient that
signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a
total....etc. Data is generated daily on a seperate sheet that I have to
count so was looking for a "portable formula" that I can paste and get
the time breakouts. Expected results 6=23 7=34 8=61 etc. (meaning
there were 23 patients between 6 am and 6:59) TIA

Biff...I will except that for some reason, it is not true Excel time. I
made a test sheet with data in A1 your original formula in B1 here are my
results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number of 6
am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF this only counts 6-6:59 I would have to paste in 12 different
formulas?? by dragging it horizontal and changing the last number to
7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every patient
that signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I
want a total....etc. Data is generated daily on a seperate sheet that I
have to count so was looking for a "portable formula" that I can paste
and get the time breakouts. Expected results 6=23 7=34 8=61 etc.
(meaning there were 23 patients between 6 am and 6:59) TIA

ok...figured it out, all answers should be 4's within the 6 am test
sheet....Tx for help, got a working solution now.

"Meebers" wrote in message
...
Biff...I will except that for some reason, it is not true Excel time. I
made a test sheet with data in A1 your original formula in B1 here are my
results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number of
6 am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF this only counts 6-6:59 I would have to paste in 12 different
formulas?? by dragging it horizontal and changing the last number to
7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every patient
that signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I
want a total....etc. Data is generated daily on a seperate sheet that
I have to count so was looking for a "portable formula" that I can
paste and get the time breakouts. Expected results 6=23 7=34 8=61
etc. (meaning there were 23 patients between 6 am and 6:59) TIA

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I'm not sure what you're doing to get those results.

Where do you want the results to appear? You said something about "dragging
horizontally" in your other reply so I'm assuming that means you want the
results across a row.

Let's assume these are your time entries:

A2 = 6:45
A3 = 6:46
A4 = 6:47
A5 = 6:51
A6 = 7:01

When you leave out the AM/PM portion of a time entry Excel defaults to AM.

Let's assume you you have some column headers that represent the hourly
intervals:

C1 = 6:00 AM
D1 = 7:00 AM
E1 = 8:00 AM

This formula entered in C2 and copied across to E2:

=SUMPRODUCT(--(HOUR($A2:$A6)=HOUR(C1)))

Returns: 4, 1, 0

Based on true Excel times.


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff...I will except that for some reason, it is not true Excel time. I
made a test sheet with data in A1 your original formula in B1 here are my
results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number of
6 am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF this only counts 6-6:59 I would have to paste in 12 different
formulas?? by dragging it horizontal and changing the last number to
7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every patient
that signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I
want a total....etc. Data is generated daily on a seperate sheet that
I have to count so was looking for a "portable formula" that I can
paste and get the time breakouts. Expected results 6=23 7=34 8=61
etc. (meaning there were 23 patients between 6 am and 6:59) TIA

Please don't post questions without questions, Biff may be a mind reader but
the rest of us are mere mortals.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"unknown" wrote:

Biff...I used your first formula "=SUMPRODUCT(--(HOUR(A1:A100)=6))" and
when I drug it down in the test sheet the formula became A2:A101and so on
giving the decrementing count. I then "fixed it" by adding the $. I am
now using the last formula you sent and everything is working as expected.
I created a sheet and left the A column blank, cut and paste from my data
sheet to the A column, and then copy/paste special/value the results and
move onto the next data sheet. Easy to get the totals for 5 am etc. Had to
change the formula to $A2:$A250. Tx for hanging in there with me.

"T. Valko" wrote in message
...
6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I'm not sure what you're doing to get those results.

Where do you want the results to appear? You said something about
"dragging horizontally" in your other reply so I'm assuming that means you
want the results across a row.

Let's assume these are your time entries:

A2 = 6:45
A3 = 6:46
A4 = 6:47
A5 = 6:51
A6 = 7:01

When you leave out the AM/PM portion of a time entry Excel defaults to AM.

Let's assume you you have some column headers that represent the hourly
intervals:

C1 = 6:00 AM
D1 = 7:00 AM
E1 = 8:00 AM

This formula entered in C2 and copied across to E2:

=SUMPRODUCT(--(HOUR($A2:$A6)=HOUR(C1)))

Returns: 4, 1, 0

Based on true Excel times.


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff...I will except that for some reason, it is not true Excel time. I
made a test sheet with data in A1 your original formula in B1 here are my
results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number of
6 am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF this only counts 6-6:59 I would have to paste in 12 different
formulas?? by dragging it horizontal and changing the last number to
7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every patient
that signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I
want a total....etc. Data is generated daily on a seperate sheet that
I have to count so was looking for a "portable formula" that I can
paste and get the time breakouts. Expected results 6=23 7=34 8=61
etc. (meaning there were 23 patients between 6 am and 6:59) TIA

Ok, glad you got it straightened out. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff...I used your first formula "=SUMPRODUCT(--(HOUR(A1:A100)=6))" and
when I drug it down in the test sheet the formula became A2:A101and so on
giving the decrementing count. I then "fixed it" by adding the $. I am
now using the last formula you sent and everything is working as expected.
I created a sheet and left the A column blank, cut and paste from my data
sheet to the A column, and then copy/paste special/value the results and
move onto the next data sheet. Easy to get the totals for 5 am etc. Had
to change the formula to $A2:$A250. Tx for hanging in there with me.

"T. Valko" wrote in message
...
6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I'm not sure what you're doing to get those results.

Where do you want the results to appear? You said something about
"dragging horizontally" in your other reply so I'm assuming that means
you want the results across a row.

Let's assume these are your time entries:

A2 = 6:45
A3 = 6:46
A4 = 6:47
A5 = 6:51
A6 = 7:01

When you leave out the AM/PM portion of a time entry Excel defaults to
AM.

Let's assume you you have some column headers that represent the hourly
intervals:

C1 = 6:00 AM
D1 = 7:00 AM
E1 = 8:00 AM

This formula entered in C2 and copied across to E2:

=SUMPRODUCT(--(HOUR($A2:$A6)=HOUR(C1)))

Returns: 4, 1, 0

Based on true Excel times.


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff...I will except that for some reason, it is not true Excel time.
I made a test sheet with data in A1 your original formula in B1 here are
my results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number
of 6 am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the
#VALUE. IF this only counts 6-6:59 I would have to paste in 12
different formulas?? by dragging it horizontal and changing the last
number to 7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every
patient that signs in at 6 am thru 6:59am I want a total. 7am thru
7:59am I want a total....etc. Data is generated daily on a seperate
sheet that I have to count so was looking for a "portable formula"
that I can paste and get the time breakouts. Expected results 6=23
7=34 8=61 etc. (meaning there were 23 patients between 6 am and
6:59) TIA

"Meebers"

1. Check whether the entries in A2:A30 are in time format.

2. Refer the cell in formula. Suppose from C2 to C13 enter 6,7,8,... and D2
enter the formula
=SUMPRODUCT(--(HOUR($A$2:$A$30)=C2)) and copy that down..

If this post helps click Yes
---------------
Jacob Skaria


"Meebers" wrote:

Biff, with times in A2:A30 I pasted in your formula and got the #VALUE. IF
this only counts 6-6:59 I would have to paste in 12 different formulas?? by
dragging it horizontal and changing the last number to 7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm and
we need to count the times that they sign in. i.e. Every patient that
signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I want a
total....etc. Data is generated daily on a seperate sheet that I have to
count so was looking for a "portable formula" that I can paste and get
the time breakouts. Expected results 6=23 7=34 8=61 etc. (meaning
there were 23 patients between 6 am and 6:59) TIA

Jacob, I used the same test data (nice spread from 6 am thru 6 pm) with
Biff's formula, the results were correct. When I used your method, same
data, I got the following answers

6 5 (correct)
7 10 (correct)
8 14 (correct)
9 0 and from here on, all counts were zero. (9 should have been 10)

Data was in time format 24 hr, formated as h:mm (this data worked in Biffs
formula), C2 thru C13 were 6,7,8...17

Thanks for your response.


"Jacob Skaria" wrote in message
...
"Meebers"

1. Check whether the entries in A2:A30 are in time format.

2. Refer the cell in formula. Suppose from C2 to C13 enter 6,7,8,... and
D2
enter the formula
=SUMPRODUCT(--(HOUR($A$2:$A$30)=C2)) and copy that down..

If this post helps click Yes
---------------
Jacob Skaria


"Meebers" wrote:

Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF
this only counts 6-6:59 I would have to paste in 12 different formulas??
by
dragging it horizontal and changing the last number to 7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and
we need to count the times that they sign in. i.e. Every patient that
signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I want
a
total....etc. Data is generated daily on a seperate sheet that I have
to
count so was looking for a "portable formula" that I can paste and get
the time breakouts. Expected results 6=23 7=34 8=61 etc. (meaning
there were 23 patients between 6 am and 6:59) TIA