excel formula - Count instances of same item within range -

excel formula - Count instances of same item within range -

setup in excel: 2 columns, identifiaction , timestamp.

what i'm trying accomplish count of every time same id has timestamp of less 1 min apart. when same id has 2 or more entries within 1 min of eachother should counted 1 instance, if id+timestamp unique should not counted.

example:

id time 1111 2014/06/18 14:03 1111 2014/06/18 15:12 1112 2014/06/18 15:03 1112 2014/06/18 15:04 1112 2014/06/18 18:03 1112 2014/06/18 18:03 1113 2014/06/18 14:02 1113 2014/06/18 14:03 1113 2014/06/18 14:03

this should result in "3" twice 1112 (once @ 15:03 , 1 time @ 18:03) , 1 time 1113.

i've tried kinds of =countif, =dcount, etc combinations. maintain getting hung @ 1 time min issue , more 2 still equals 1 count.

hope can help (probably simple) solution.

thanks.

1) order data, first criteria id; sec criteria time

2) in new column starting in sec row: =if( and(upper_cell.id=this_row.id;abs(upper_cell.time - this_row.time)<=(1/(24*60))) ; 1 ; 0)

3) add together values of new column

excel-formula