Count cells in column that contain number in a range

[az willie]

I have a column of times in 24 hour format. I want to have a formula that
counts the number of cells in the column that contain a time in a certain
range.

That is if the cell contains a time between 22:00 and 23:00 the cell should
be counted. I will have to do that for each hour.

I am trying to determine how many times during the night events happen in
each time period. So I want to have a formula that counts the cells that
fall within the time periods.

I have tried =COUNTIF(I47:I3846,">23:01:<24:00")but it just returns zeros
and I know there are cells with times in that range.

All help / suggestions / solutions / jokes appreciated

 

[Glenn]

I have a column of times in 24 hour format. I want to have a formula that
counts the number of cells in the column that contain a time in a certain
range.

That is if the cell contains a time between 22:00 and 23:00 the cell should
be counted. I will have to do that for each hour.

I am trying to determine how many times during the night events happen in
each time period. So I want to have a formula that counts the cells that
fall within the time periods.

I have tried =COUNTIF(I47:I3846,">23:01:<24:00")but it just returns zeros
and I know there are cells with times in that range.

All help / suggestions / solutions / jokes appreciated

You could use a PivotTable. Put the Times in the Row and Data Items areas. In
the Row area, group by Hours.

 

[Mike H]

Try this

format as general

=COUNTIF(I47:I3846,">"&TIME(23,1,0))-COUNTIF(I47:I3846,"<"&TIME(24,0,0))

Mike

 

[az willie]

=COUNTIF(I47:I3846,">"&TIME(23,1,0))-COUNTIF(I47:I3846,"<"&TIME(24,0,0))

=================
That comes real close to working. I get a count but some of the numbers
are negative!!!

And, the numbers generated add up to way more than the actual count.

I had a column for events and one for the time and I summed the event
column and got 399 but some of the hour counts are almost that high.
I am < really > puzzled at getting negative numbers for some hours.

The 10 pm to 11 pm and the 6 am to 7am and 7 am to 8 am generate negative
numbers.

I am still doing something wrong because I am getting too high a count.

I can't seem to get the formulas to paste correctly. I copied two colums.
One column has the formula and the next has the time span. When I paste
it, everything is getting mashed together. But I think you can see the
formulas ok. What have I done wrong here?

=COUNTIF(I4:I3846,">"&TIME(22,1,0))-COUNTIF(I4:I3846,"<"&TIME(23,0,0))
10 PM TO 11 PM
=COUNTIF(I4:I3846,">"&TIME(23,1,0))-COUNTIF(I4:I3846,"<"&TIME(24,0,0))
11PM TO 12 PM
=COUNTIF(I4:I3846,">"&TIME(0,1,0))-COUNTIF(I4:I3846,"<"&TIME(1,0,0)) 12PM
TO 1 AM
=COUNTIF(I4:I3846,">"&TIME(1,1,0))-COUNTIF(I4:I3846,"<"&TIME(2,0,0)) 1 AM
TO 2 AM
=COUNTIF(I4:I3846,">"&TIME(2,1,0))-COUNTIF(I4:I3846,"<"&TIME(3,0,0)) 2 AM
TO 3 AM
=COUNTIF(I4:I3846,">"&TIME(3,1,0))-COUNTIF(I4:I3846,"<"&TIME(4,0,0)) 3 AM
TO 4 AM
=COUNTIF(I4:I3846,">"&TIME(4,1,0))-COUNTIF(I4:I3846,"<"&TIME(5,0,0)) 4 AM
TO 5 AM
=COUNTIF(I4:I3846,">"&TIME(5,1,0))-COUNTIF(I4:I3846,"<"&TIME(6,0,0)) 5 AM
TO 6 AM
=COUNTIF(I4:I3846,">"&TIME(6,1,0))-COUNTIF(I4:I3846,"<"&TIME(7,0,0)) 6 AM
TO 7 AM
=COUNTIF(I4:I3846,">"&TIME(7,1,0))-COUNTIF(I4:I3846,"<"&TIME(8,0,0)) 7 AM
TO 8 AM

 

[T. Valko]

Need some clarification...

Usually, when people say count between 10pm and 11pm what they really mean
is count *from* 10pm to 11pm. Counting "from" means to include both 10pm and
11pm. Counting "between" technically means to exclude both 10pm and 11pm.

 

[az willie]

Need some clarification...

Usually, when people say count between 10pm and 11pm what they really
mean is count *from* 10pm to 11pm. Counting "from" means to include
both 10pm and 11pm. Counting "between" technically means to exclude
both 10pm and 11pm.

===================
Yes I understand what you're asking I believe.

I want to count the first hour from 10:00PM to 11:00 pm.
Then the second hour would go from 11:01pm to 12:00pm.
Then from 00.01 AM to 01:00 am.

If an event happened at 10:00pm it would be counted in the first hour as
would an event at 11:00pm.

Ah I think I see a fallacy in my thinking. It should probably go from
10:00pm to 10:59pm then from 11:00 to 11:59pm etc. etc.

That may be why the results are so high. Events are possibly being double
counted.

But I don't understand the negative numbers being returned in 3 cases.
Hmmmm.

 

[az willie]

===================
Yes I understand what you're asking I believe.

I want to count the first hour from 10:00PM to 11:00 pm.
Then the second hour would go from 11:01pm to 12:00pm.
Then from 00.01 AM to 01:00 am.

If an event happened at 10:00pm it would be counted in the first hour
as would an event at 11:00pm.

Ah I think I see a fallacy in my thinking. It should probably go from
10:00pm to 10:59pm then from 11:00 to 11:59pm etc. etc.

That may be why the results are so high. Events are possibly being
double counted.

But I don't understand the negative numbers being returned in 3
cases. Hmmmm.

===============
Okay I changed the formulas so they go from 10:00 to 10:59 etc. etc. and
now I've got five of them returning negative numbers!! Very strange.

I've tried to check the formulas very very carefully and I see no
difference between the ones that generate positive numbers and the ones
that generate negative numbers. But obviously something is wrong
somewhere.

Good thing I'm bald already ... this could make a guy tear his hair out

 

[T. Valko]

I'm assuming you deal with precision to the minute and not to the second.

Ok, to count *from* 10pm to 10:59pm:

=COUNTIF(I47:I3846,">="&TIME(22,0,0))-COUNTIF(I47:I3846,">"&TIME(22,59,0))

Better if you put the time boundaries in cells:

A1 = 10:00 PM (or 22:00)
B1 = 10:59 PM (or 22:59)

=COUNTIF(I47:I3846,">="&A1)-COUNTIF(I47:I3846,">"&B1)

 

[az willie]

I'm assuming you deal with precision to the minute and not to the second.

Ok, to count *from* 10pm to 10:59pm:

=COUNTIF(I47:I3846,">="&TIME(22,0,0))-COUNTIF(I47:I3846,">"&TIME(22,59,0))

Better if you put the time boundaries in cells:

A1 = 10:00 PM (or 22:00)
B1 = 10:59 PM (or 22:59)

=COUNTIF(I47:I3846,">="&A1)-COUNTIF(I47:I3846,">"&B1)

===========
Sorry for the delay in responding. Had a minor surgery yesterday on my foot
and the anesthesia had me knocked out all day and still not functioning
real good.

I will check out using cells as you showed when I get back up to par.

Your help is much appreciated. I just started this project at a bad time.
Didn't realize the minor surgery would knock me out so bad. Not to mention
the owie and the pain pills that don't seem to do too much.

Oh well ... this too shall pass and I will conquer the evil spreadsheet.

 

[T. Valko]

Sounds like you won't be running in a marathon any time soon!

Good luck on your recovery.

Here's yet another option:

=SUMPRODUCT(--(HOUR(I47:I3846)=22))

That covers from 10:00:00 PM to 10:59:59 PM

Just be aware that from 12:00:00 AM to 12:59:59 AM is hour 0. That will also
evaluate empty cells as 0 and could be counted as hour 0. To account for
that:

=SUMPRODUCT(--(I47:I3846<>""),--(HOUR(I47:I3846)=22))