Formula help - sum data from hours to date

Occasional Contributor

I need to sum hourly registration per category (T, S, C, U) on the actual date (see the full dataset attached). Any code help in the attached Excelfile is appreciated!

Lars

 

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

EXAMPLE:

This is how I want my result:

DateTSCUTotal
2021-07-0657170478

 

(Data per hour on that date)

DateHourTSCUTotal
2021-07-060:0000000
2021-07-061:0000000
2021-07-062:0000000
2021-07-063:0012003
2021-07-064:0011002
2021-07-065:0022004
2021-07-066:0030003
2021-07-067:0070007
2021-07-068:0042006
2021-07-069:0040004
2021-07-0610:0072009
2021-07-0611:0051006
2021-07-0612:0051006
2021-07-0613:0020002
2021-07-0614:0000000
2021-07-0615:0010001
2021-07-0616:0032016
2021-07-0617:0052007
2021-07-0618:0032016
2021-07-0619:0020024
2021-07-0620:0020002
2021-07-0621:0000000
2021-07-0622:0000000
2021-07-0623:0000000

 

2 Replies

@LBendix 

=SUMPRODUCT(($I2=$H$2:$H$2729)*(J$1=$C$1:$F$1)*$C$2:$F$2729)

Maybe with SUMPRODUCT. I've added a (hidden) helper column H which has dates in the format "03.07.2021" instead of "03.07.2021 07:00:00".

Thank you, this solved it!