Calculating total hours when there are duplicate times?

[JV63]

Hi. I have a spreadsheet that has times such as:

11:59 PM
11:58 PM
11:58 PM
11:56 PM
11:55 PM
11:55 PM
11:55 PM

And so on. I'm trying to calculate the total hours in the column but not count the duplicates. I need them so I can't get rid of them but is it a matter of copying the times, removing duplicates and then calculating total time?

Thanks

 

[Kaper]

1) remember to set custom formatting for the cell with formula (as you probably exceed 24hrs). For instance such format: "[hh]:mm"

use formula (here sample for 12 rows):

=SUMPRODUCT($A$1:$A$12/COUNTIF($A$1:$A$12,$A$1:$A$12))

or, if there are in the range empty cells, or cells with texts, use array formula (committed with Ctrl+Shift+Enter):

=SUM(IFERROR($A$1:$A$12/COUNTIF($A$1:$A$12,$A$1:$A$12),0))