Forum Discussion
Need help to find the right code/ function
I have a relatively large dataset with registrations (numbers) from a specific time, each hour for many months (just a small sample shown here).
I want to sum up every number that is registered at every hour during the whole period.
Which function/ code can I use for that?
| Time | Number of registrations | No of registrations/ 03:00 | No of registrations/ 04:00 | No of registrations/ 05:00 | etc… |
| 2021-07-01 03:00 | 1 | ? | ? | ? | ? |
| 2021-07-01 04:00 | 0 | ||||
| 2021-07-01 05:00 | 0 | ||||
| 2021-07-01 06:00 | 0 | ||||
| 2021-07-01 07:00 | 1 | ||||
| 2021-07-01 08:00 | 1 | ||||
| 2021-07-01 09:00 | 1 | ||||
| 2021-07-01 10:00 | 14 | ||||
| 2021-07-01 11:00 | 2 | ||||
| 2021-07-01 12:00 | 1 | ||||
| 2021-07-01 13:00 | 0 | ||||
| 2021-07-01 14:00 | 0 | ||||
| 2021-07-01 15:00 | 2 | ||||
| 2021-07-01 16:00 | 0 | ||||
| 2021-07-01 17:00 | 0 | ||||
| 2021-07-01 18:00 | 1 | ||||
| 2021-07-01 19:00 | 0 | ||||
| 2021-07-01 20:00 | 0 | ||||
| 2021-07-01 21:00 | 1 | ||||
| 2021-07-01 22:00 | 0 | ||||
| 2021-07-01 23:00 | 0 | ||||
| 2021-07-02 00:00 | 0 | ||||
| 2021-07-02 01:00 | 0 | ||||
| 2021-07-02 02:00 | 0 | ||||
| 2021-07-02 03:00 | 0 | ||||
| 2021-07-02 04:00 | 1 | ||||
| 2021-07-02 05:00 | 2 | ||||
| 2021-07-02 06:00 | 2 | ||||
| 2021-07-02 07:00 | 0 | ||||
| 2021-07-02 08:00 | 2 | ||||
| 2021-07-02 09:00 | 0 | ||||
| 2021-07-02 10:00 | 0 | ||||
| 2021-07-02 11:00 | 0 | ||||
| 2021-07-02 12:00 | 1 | ||||
| 2021-07-02 13:00 | 1 | ||||
| 2021-07-02 14:00 | 0 | ||||
| 2021-07-02 15:00 | 0 | ||||
| 2021-07-02 16:00 | 1 | ||||
| 2021-07-02 17:00 | 0 | ||||
| 2021-07-02 18:00 | 0 | ||||
| 2021-07-02 19:00 | 0 | ||||
| 2021-07-02 20:00 | 1 | ||||
| 2021-07-02 21:00 | 0 | ||||
| 2021-07-02 22:00 | 0 | ||||
| 2021-07-02 23:00 | 0 | ||||
| 2021-07-03 00:00 | 0 | ||||
| 2021-07-03 01:00 | 0 | ||||
| 2021-07-03 02:00 | 1 | ||||
| 2021-07-03 03:00 | 0 | ||||
| 2021-07-03 04:00 | 1 | ||||
| 2021-07-03 05:00 | 0 | ||||
| 2021-07-03 06:00 | 4 | ||||
| 2021-07-03 07:00 | 0 | ||||
| 2021-07-03 08:00 | 2 | ||||
| 2021-07-03 09:00 | 2 | ||||
| 2021-07-03 10:00 | 0 | ||||
| 2021-07-03 11:00 | 2 | ||||
| 2021-07-03 12:00 | 0 | ||||
| 2021-07-03 13:00 | 2 | ||||
| 2021-07-03 14:00 | 3 | ||||
| 2021-07-03 15:00 | 0 | ||||
| 2021-07-03 16:00 | 0 | ||||
| 2021-07-03 17:00 | 0 | ||||
| 2021-07-03 18:00 | 1 | ||||
| 2021-07-03 19:00 | 0 | ||||
| 2021-07-03 20:00 | 3 | ||||
| 2021-07-03 21:00 | 2 | ||||
| 2021-07-03 22:00 | 1 | ||||
| 2021-07-03 23:00 | 1 | ||||
| 2021-07-04 00:00 | 1 |
Working with your sample, I first put in row 1, the hours 0, 1, 2, 3, 4, 5, 6....etc
Then in Row 2 the following formula, referring to Row 1 for the hours
=SUM(FILTER($B2:$B71,HOUR($A2:$A71)=C1,0))
And that gets the totals for each hour. Here's a screen grab, but I've also attached the spreadsheet.
2 Replies
Working with your sample, I first put in row 1, the hours 0, 1, 2, 3, 4, 5, 6....etc
Then in Row 2 the following formula, referring to Row 1 for the hours
=SUM(FILTER($B2:$B71,HOUR($A2:$A71)=C1,0))
And that gets the totals for each hour. Here's a screen grab, but I've also attached the spreadsheet.
- Thank you very much, mathetes!
This solved my problem!!
