Sum column according to another in excel-CodePudding

I have for each line i have 2 columns :

I need to extract for each #/CITY/BUILDING/FLOOR the sum of time spent

#/MAIN/063/00/024.  00:30
#/MAIN/059/00/024   04:00
#/MAIN/059/00/022.  01:30
#/MAIN/027/01/022.  01:00

So, i need to sum all time for each building floor in my example :

#/MAIN/063/00  00:30
#/MAIN/027/01. 01:00
#/MAIN/059/00. 05:30

CodePudding user response：

It's quite possible with Microsoft365's dynamic arrays:

Formula in D1:

=CHOOSE({1,2},UNIQUE(LEFT(A1:A4,13)),SUMIF(A1:A4,UNIQUE(LEFT(A1:A4,13))&"*",B1:B4))

Note that column in is a custom number-format: [HH]:MM