# Count time (entries per hour)

1. ## Count time (entries per hour)

I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time)

For 12:00:00 all times would be between and including 12:00:00 and 12:59:59

Column B | Count
------------------
12:00:00 344
13:00:00 44
14:00:00 5

Thanks a million!

2. I used SUMPRODUCT. I assume all cells where you have times are formatted as time and not text. I attached my example. With the Data in A1:A50, the time reference in column C and the formula in D.
``Please Login or Register  to view this content.``
Does this work for you?

I tried that formula. It seems to be putting the counts that fall on the hour exactly following with the previous hour. If that doesn't make any sense...

12:11
12:21
12:24
12:33
12:44
12:55
13:00
13:00
13:10
13:23

The count for the 12:00 hour should be: 6
The count for the 13:00 hour should be: 4

That formula is showing a 12:00 total of 8 (including the two 13:00's) and 2 for 13:00.
It seems to be doing... 12:00:01 to 13:00:00

ideas?

4. Just move the = in ChemistB's suggestion, i.e. change to

=SUMPRODUCT(--(\$A\$1:\$A\$50<C2)*(\$A\$1:\$A\$50>=C1))

5. great... that works for all hours except 0:00.

The result should be only 100 but it's including all empty cells as 0:00 apparently.

Is there a "only include cells with data" in the count that we can add into that one?

6. You can filter out blanks

=SUMPRODUCT(--(\$A\$1:\$A\$50<C2),--(\$A\$1:\$A\$50>=C1),--(\$A\$1:\$A\$50<>""))

7. that's it! You guys rock!

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1