Forum Discussion
Solved
Need assist with formula
I have a list of registrations and want to count the number in each category (T, S) that occurs at every whole hour. If no registration at a certain time, it should fill inn "0". (Dataset is below)
Want it to look like this (example):
| Category | |||
| Date | Time | T | S |
| 2021-07-04 | 00:00 | 1 | |
| 2021-07-04 | 01:00 | 1 | |
| 2021-07-04 | 02:00 | 1 | 1 |
| 2021-07-04 | 03:00 | 1 | |
| 2021-07-04 | 04:00 | 3 | |
| 2021-07-04 | 05:00 | 2 | |
Dataset:
| Date | Hour | Category |
| 2021-07-04 | 00.38 | T |
| 2021-07-04 | 01.04 | T |
| 2021-07-04 | 02.01 | S |
| 2021-07-04 | 02.33 | T |
| 2021-07-04 | 03.11 | T |
| 2021-07-04 | 04.05 | T |
| 2021-07-04 | 04.06 | T |
| 2021-07-04 | 04.23 | T |
| 2021-07-04 | 05.28 | T |
| 2021-07-04 | 05.28 | T |
| 2021-07-04 | 06.09 | T |
| 2021-07-04 | 06.33 | T |
| 2021-07-04 | 07.03 | T |
| 2021-07-04 | 07.09 | S |
| 2021-07-04 | 07.51 | T |
| 2021-07-04 | 07.56 | T |
| 2021-07-04 | 08.17 | T |
| 2021-07-04 | 08.18 | T |
| 2021-07-04 | 08.29 | T |
| 2021-07-04 | 08.44 | T |
| 2021-07-04 | 09.05 | T |
| 2021-07-04 | 09.05 | T |
| 2021-07-04 | 09.23 | T |
| 2021-07-04 | 10.34 | T |
| 2021-07-04 | 10.38 | T |
| 2021-07-04 | 11.22 | S |
| 2021-07-04 | 11.40 | T |
| 2021-07-04 | 11.54 | T |
| 2021-07-04 | 12.16 | T |
| 2021-07-04 | 12.30 | S |
| 2021-07-04 | 13.17 | T |
| 2021-07-04 | 14.42 | S |
| 2021-07-04 | 15.59 | T |
| 2021-07-04 | 16.42 | S |
| 2021-07-04 | 20.01 | T |
| 2021-07-04 | 20.55 | T |
| 2021-07-04 | 21.07 | T |
| 2021-07-04 | 21.13 | T |
| 2021-07-04 | 21.39 | T |
| 2021-07-04 | 22.18 | T |
| 2021-07-04 | 22.22 | T |
| 2021-07-04 | 22.22 | T |
| 2021-07-04 | 22.29 | T |
| 2021-07-04 | 22.50 | T |
| 2021-07-04 | 22.53 | T |
| 2021-07-04 | 23.41 | T |
| 2021-07-05 | 00.33 | T |
| 2021-07-05 | 01.37 | T |
| 2021-07-05 | 02.29 | T |
| 2021-07-05 | 03.07 | S |
| 2021-07-05 | 04.07 | T |
| 2021-07-05 | 04.07 | T |
| 2021-07-05 | 04.33 | S |
| 2021-07-05 | 04.48 | S |
| 2021-07-05 | 05.05 | T |
| 2021-07-05 | 05.56 | T |
| 2021-07-05 | 06.19 | T |
| 2021-07-05 | 06.58 | S |
| 2021-07-05 | 07.12 | S |
| 2021-07-05 | 07.52 | S |
| 2021-07-05 | 06.54 | T |
| 2021-07-05 | 08.32 | S |
| 2021-07-05 | 08.41 | T |
| 2021-07-05 | 09.03 | T |
| 2021-07-05 | 09.16 | T |
| 2021-07-05 | 09.18 | T |
| 2021-07-05 | 09.34 | T |
| 2021-07-05 | 09.57 | T |
| 2021-07-05 | 10.21 | T |
| 2021-07-05 | 10.31 | S |
| 2021-07-05 | 10.43 | T |
| 2021-07-05 | 10.58 | T |
| 2021-07-05 | 11.10 | T |
| 2021-07-05 | 12.26 | S |
| 2021-07-05 | 12.42 | T |
| 2021-07-05 | 14.19 | S |
| 2021-07-05 | 15.01 | T |
| 2021-07-05 | 15.04 | S |
| 2021-07-05 | 15.40 | T |
| 2021-07-05 | 15.46 | T |
| 2021-07-05 | 15.48 | T |
| 2021-07-05 | 15.42 | T |
| 2021-07-05 | 16.04 | T |
| 2021-07-05 | 16.27 | S |
| 2021-07-05 | 16.48 | T |
| 2021-07-05 | 17.27 | T |
| 2021-07-05 | 17.29 | T |
| 2021-07-05 | 17.48 | T |
| 2021-07-05 | 18.20 | T |
| 2021-07-05 | 18.27 | T |
| 2021-07-05 | 18.36 | T |
| 2021-07-05 | 18.46 | T |
| 2021-07-05 | 19.01 | T |
| 2021-07-05 | 19.31 | S |
| 2021-07-05 | 19.38 | T |
| 2021-07-05 | 20.01 | T |
| 2021-07-05 | 20.02 | S |
| 2021-07-05 | 20.51 | S |
| 2021-07-05 | 21.28 | S |
| 2021-07-05 | 21.45 | S |
| 2021-07-05 | 21.50 | T |
| 2021-07-05 | 22.08 | S |
| 2021-07-05 | 22.12 | S |
| 2021-07-05 | 22.17 | T |
| 2021-07-05 | 22.20 | T |
| 2021-07-05 | 22.28 | S |
| 2021-07-05 | 22.44 | T |
| 2021-07-05 | 22.54 | T |
| 2021-07-05 | 23.52 | T |
| 2021-07-06 | 01.01 | S |
| 2021-07-06 | 01.07 | S |
| 2021-07-06 | 03.19 | T |
| 2021-07-06 | 03.20 | S |
| 2021-07-06 | 04.10 | S |
| 2021-07-06 | 04.13 | T |
| 2021-07-06 | 05.14 | T |
| 2021-07-06 | 05.23 | T |
| 2021-07-06 | 05.46 | S |
| 2021-07-06 | 05.53 | S |
| 2021-07-06 | 06.04 | T |
| 2021-07-06 | 06.42 | T |
| 2021-07-06 | 06.56 | T |
| 2021-07-06 | 07.08 | T |
| 2021-07-06 | 07.29 | T |
| 2021-07-06 | 07.29 | T |
| 2021-07-06 | 07.37 | T |
| 2021-07-06 | 07.37 | T |
| 2021-07-06 | 07.37 | T |
| 2021-07-06 | 07.41 | T |
| 2021-07-06 | 07.41 | T |
| 2021-07-06 | 07.49 | T |
| 2021-07-06 | 07.51 | T |
| 2021-07-06 | 07.57 | T |
| 2021-07-06 | 08.00 | T |
| 2021-07-06 | 08.00 | T |
| 2021-07-06 | 08.06 | S |
| 2021-07-06 | 08.06 | T |
| 2021-07-06 | 08.08 | T |
| 2021-07-06 | 08.11 | T |
| 2021-07-06 | 08.38 | T |
| 2021-07-06 | 09.08 | T |
| 2021-07-06 | 09.11 | T |
| 2021-07-06 | 10.04 | T |
| 2021-07-06 | 10.05 | T |
| 2021-07-06 | 10.06 | T |
| 2021-07-06 | 10.20 | T |
| 2021-07-06 | 10.26 | T |
| 2021-07-06 | 10.39 | T |
| 2021-07-06 | 10.40 | T |
| 2021-07-06 | 10.42 | S |
| 2021-07-06 | 10.45 | T |
| 2021-07-06 | 11.05 | T |
| 2021-07-06 | 11.18 | T |
| 2021-07-06 | 11.18 | T |
| 2021-07-06 | 11.28 | T |
| 2021-07-06 | 11.41 | T |
| 2021-07-06 | 11.45 | S |
| 2021-07-06 | 12.26 | T |
| 2021-07-06 | 12.29 | T |
| 2021-07-06 | 12.34 | S |
| 2021-07-06 | 12.44 | T |
| 2021-07-06 | 12.49 | T |
| 2021-07-06 | 12.55 | T |
| 2021-07-06 | 13.33 | T |
| 2021-07-06 | 15.20 | T |
| 2021-07-06 | 16.16 | T |
| 2021-07-06 | 16.21 | S |
| 2021-07-06 | 16.23 | T |
| 2021-07-06 | 16.30 | T |
| 2021-07-06 | 16.52 | S |
| 2021-07-06 | 17.03 | T |
| 2021-07-06 | 17.04 | T |
| 2021-07-06 | 17.28 | T |
| 2021-07-06 | 17.29 | T |
| 2021-07-06 | 17.38 | T |
| 2021-07-06 | 17.38 | T |
| 2021-07-06 | 18.00 | T |
| 2021-07-06 | 18.14 | T |
| 2021-07-06 | 18.15 | T |
| 2021-07-06 | 18.23 | S |
| 2021-07-06 | 18.58 | T |
| 2021-07-06 | 18.59 | T |
| 2021-07-06 | 19.10 | T |
| 2021-07-06 | 19.23 | T |
| 2021-07-06 | 19.39 | T |
| 2021-07-06 | 19.50 | T |
| 2021-07-06 | 20.14 | T |
| 2021-07-06 | 20.27 | T |
| 2021-07-06 | 22.00 | T |
| 2021-07-06 | 23.52 | T |
| 2021-07-07 | 01.01 | S |
| 2021-07-07 | 01.07 | S |
| 2021-07-07 | 03.19 | T |
| 2021-07-07 | 03.20 | S |
| 2021-07-07 | 04.10 | S |
| 2021-07-07 | 04.13 | T |
| 2021-07-07 | 05.04 | S |
| 2021-07-07 | 05.14 | T |
| 2021-07-07 | 05.46 | S |
| 2021-07-07 | 05.53 | S |
| 2021-07-07 | 06.04 | T |
| 2021-07-07 | 06.42 | T |
| 2021-07-07 | 06.56 | T |
| 2021-07-07 | 07.08 | T |
| 2021-07-07 | 07.29 | T |
| 2021-07-07 | 07.29 | T |
| 2021-07-07 | 07.37 | T |
| 2021-07-07 | 07.37 | T |
| 2021-07-07 | 07.37 | T |
| 2021-07-07 | 07.40 | S |
| 2021-07-07 | 07.43 | S |
| 2021-07-07 | 07.44 | T |
| 2021-07-07 | 08.00 | T |
| 2021-07-07 | 08.10 | T |
| 2021-07-07 | 08.16 | S |
| 2021-07-07 | 08.19 | T |
| 2021-07-07 | 08.36 | S |
| 2021-07-07 | 08.48 | T |
| 2021-07-07 | 09.10 | T |
| 2021-07-07 | 09.18 | T |
| 2021-07-07 | 09.28 | T |
| 2021-07-07 | 09.34 | T |
| 2021-07-07 | 09.46 | T |
| 2021-07-07 | 10.03 | S |
| 2021-07-07 | 10.19 | S |
| 2021-07-07 | 10.24 | S |
| 2021-07-07 | 10.53 | T |
| 2021-07-07 | 11.39 | T |
| 2021-07-07 | 11.43 | T |
| 2021-07-07 | 13.43 | T |
| 2021-07-07 | 14.07 | T |
| 2021-07-07 | 14.54 | T |
| 2021-07-07 | 15.07 | T |
| 2021-07-07 | 17.56 | T |
| 2021-07-07 | 18.01 | T |
| 2021-07-07 | 18.15 | S |
| 2021-07-07 | 19.19 | T |
| 2021-07-07 | 19.45 | T |
| 2021-07-07 | 19.49 | S |
| 2021-07-07 | 19.49 | T |
| 2021-07-07 | 20.07 | T |
| 2021-07-07 | 20.41 | T |
| 2021-07-07 | 21.45 | T |
| 2021-07-08 | 06.00 | T |
| 2021-07-08 | 08.14 | T |
| 2021-07-08 | 08.27 | T |
| 2021-07-08 | 08.49 | T |
| 2021-07-08 | 09.01 | T |
| 2021-07-08 | 09.10 | T |
| 2021-07-08 | 09.21 | T |
| 2021-07-08 | 09.51 | T |
| 2021-07-08 | 09.52 | S |
| 2021-07-08 | 09.52 | T |
See the attached version. It uses SUMPRODUCT, not COUNTIF.
6 Replies
You can add a working column 'Round hour' next to your data
Then using HOUR function you can get the round (whole hours).
Then you can summarize the results with COUNTIF or pivot table
I used the HOUR and COUNTIF to get half way, but still need input on the code to summarize/ sum up the numbers each hour/ date. Please see the attached dataset
Any input is appreciated. Thanks again!See the attached version. It uses SUMPRODUCT, not COUNTIF.
One option is a pivottable - see the attached sample workbook.
I added an auxiliary column "Hour (rounded)" to the source data, and used that instead of the Hour column in the pivot table.
I changed the pivot table to tabular format, specified that it should have no subtotals and grand totals, and that labels should be repeated.
- Not very familiar with pivot unfortunately, but thanks anyway!
