Google Spreadsheet Post #1069

*Yogi Anand, D.Eng, P.E.*ANAND Enterprises LLC -- Rochester Hills MI

*www.energyefficientbuild.com. Mar 7, 2013*

*user Somewhat C*

*:(*

*http://productforums.google.com/forum/?zx=dk50ymjgzrap#!category-topic/docs/spreadsheets/lKCccEmuWzg*

*)*

*Counting frequency of different times of day*

*I have some data that needs to be counted and I'm unsure how to do it the way that I want. Column A has date and time of occurrences. I need to count how many occurrences took place between:*

*0:00:00 - 1:00:00*

*1:00:00 - 2:00:00*

*2:00:00 - 3:00:00*

*and so forth.*

I have left sheet "data" as the data input only sheet and the sheet "Count" to do the counting on.

I have left sheet "data" as the data input only sheet and the sheet "Count" to do the counting on.

*The date portion of this isn't important and can be ignored. I only care about counting the times.*

Any help on this would be greatly appreciated. The Spreadsheet in question is: https://docs.google.com/spreadsheet/ccc?key=0As849t1zRsg1dFIxSUkzNHZ1a3RpRUN1ZW55d0E1MkE#gid=0

Any help on this would be greatly appreciated. The Spreadsheet in question is: https://docs.google.com/

------------------------------------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem

1) my solution is an array formula that furnishes the range of hours and the related count of transactions in a

single formula

2) I have shown the count for each of the 24 hours in Sheet1 itself;

in my results, 24th hour is shown as hour 0

3) there is a difference between my computed results and Somewhat C's expected results ... so let us check those

to see where the problem might be

(by the way I did run a simple check ... the TimeStamp column has 3247 dates in range A2:A3248 ... and the

total number of transactions for all hours taken together in my case is 3247 -- which makes sense, but the

count of transactions from Somewhat C's posted results for 3247 dates is 3835 -- which doesn't seem right ...

but let us not jump the gun, let Somewhat C check it out and then take it from there)

there were over 3200 rows of data in the user's spreadsheet which I had originally used in this blog post ...

however, for this illustration, I have now reduced the number of rows to 40